How to Calculate Hours Worked in Excel (Midnight Span)

We’ll carry on with the tutorial on how to calculate hours worked after a night shift. There’s a specific formula to total hours worked past midnight in Excel, but after watching this video, it’ll be a piece of cake!

In the last video tutorial, we had a look at how to calculate hours worked if the ‘Start Work’ and ‘End Work’ times are within the same day. We also calculated working time for each day, from Monday to Friday. The problem is, that this usual way of calculating hours will not work for you if the working time spans midnight, which is the case of Friday.

On Friday, work started at seven o’clock in the evening and finished the next day, at six o’clock in the morning. Since the working time spanned midnight, which means the employee didn’t finish work on the same day, we need to use the function IF to calculate the hours correctly.

Let’s do this together!

Watch next video tutorial:

Time Calculation In Excel:

Excel Alignment Formatting

How to Use IF Function in Excel (Step by Step)

================

❤️ Become a Patron:
Do you find our tutorials useful? Join this channel and become a patron

================

⏱️Timestamps⏱️
0:00 How to Calculate Hours Worked in Excel (Midnight Span)
1:11 How to Use If Function in Excel

================

Subscribe and watch more videos that help you use Microsoft Excel quick and easy

Got Microsoft Office 365? Get it here

Transcription How to Calculate Hours Worked in Excel (Midnight Span) here

Connect:

Screen Recorder & Video Editor:

#MicrosoftExcelTutorial #ExcelQuickAndEasy #EasyClickAcademy

[joshbracken]

Thank you so much. I searched through so many videos for this answer. Thanks again!!

[tonystark-kqdl]

i love you already, you just saved a graphic artist whos trying to learn another job.

[oldtrojanskin]

You Sir are the Boss. My finish time is midnight and this formula worked fantastically. Thank you for this video.

[natasjavanstaden]

THANK YOU, THIS WAS SO EASY TO FOLLOW, AND TO APPLY TO MY WORK SHEET, THANK YOU AGAIN

[theplunge]

Thank you this helped so much, I am a nightshift nurse and this spreadsheet helped me find some missing hours in my paycheck!

[rexx]

Dude, u saved me so lot of time! ❤ u got a new loyal subscriber. 💋

[nauruakoura]

this was very helpful in fasten my work while working with hours of shift staff for Appraising purposes mainly in the analyzation of their attendances.... Well done

[rajkishorebehera]

Very useful thank u sir😊😊 I was tensed from yesterday and now I am relaxed 😘😘

[MedooWolf]

Try the “Mod” function for example =mod((time out - time in), 1)

[alanw]

Very clear and easy to understand — thank you!! 🙏🏻

[diamondbreak]

You are such a life saver! tons of thanks.

[vanz-]

Hey there great video found one problem with the equation if the clock in time is 6:00 Am and clock out time is 6:00 Pm the same time but am and pm the output would be 0.5 to fix this problem I used this equation =(IF(D4>D5, D5+1, D5)-D4)*24 this will return the right output. Just add the times 24 at the end to fix this problem. Furthermore this equation does no work for 24 hours shifts which I doubt people do but it a thing. Again Great video just trying to provide more help.

[AliAbbas-uhoy]

Thanks, sir, very informative video.

[adlenemarsita]

Awesome! Thank you for making it so clear

[soumiamoujane]

Thank you for this video. Very helpful and useful.

[akkiannetolentino]

thank you so much, im suffer this for how many years to. make my time sheet when comes to overlapping time.

[philbrenman]

I have followed your instructions to the letter and I continue to get an Error popup. Very frustrating.

[chaithanyaasokan]

This was very helpful to understand timesheet... is there a video on salary calculation?

[syedmubarakbasha]

thank u very Much sir such a great video.

[Zlx]

This works perfectly, but i have a different problem related to this.
If say the work hours are from 17:00-07:00, with this tutorial it totals at 14 hours which is correct. But what if i want to automate the total amount of hours worked that has elapsed 18:00 but within 06:00?
Where I'm from we get paid extra for the hours worked passed 18:00 and stops after 06:00, so with my 14 hours worked only 12 hours are within that time frame. Is there a formula for this?