# Excel Magic Trick 1206: Day & Night Shift Time Calculations: Add Total Hours Single Cell Formula

Video duration: 10:35
See how to create a single cell formula that calculates the total hours worked for the week given a column of start times and a column of end times:
1. (00:10) Problem Setup
2. (01:00) Time Values in Excel are decimals between 0 and 1 that represent the proportion of a 24 hour day.
3. (02:13) Basic Time Calculation: End Time – Start Time
4. (03:11) IF formula for Day & Night Shift Time Calculations
5. (03:55) MOD function for Day & Night Shift Time Calculations
6. (05:00) Explanation of MOD algorithm: n-d*INT(d/n)
7….

## 22 thoughts on “Excel Magic Trick 1206: Day & Night Shift Time Calculations: Add Total Hours Single Cell Formula”

#### Habibur Rahman

(July 20, 2018 - 10:54 pm)

a man work today 10 am to next day 8 am total 22 hours ….how to be its counts in excel

#### Bobbi George

(July 20, 2018 - 10:54 pm)

Mike how would one go about getting the single cell to display nothing when there is no data entered into columns B and C using this method? my current formula looks like this;

=SUMPRODUCT(MOD(M2:M11-L2:L11-N2:N11,1)*24)

** The added column (N2:N11) being the subtracting of breaks. **

Thank you for your video, it's helped me big time : )

#### Wasif Chaudhry

(July 20, 2018 - 10:54 pm)

Wonderful thank you so much.

#### Daniel Sparks

(July 20, 2018 - 10:54 pm)

I love your videos and have learned quite a lot from them, thank you. I would like to ask for some help however. I work a 24 hour shift which at times runs over, but also can run short as in 12 am. How can I format an "if" exception for my formula to work every time?

#### Abdul Basith Ashraf

(July 20, 2018 - 10:54 pm)

Its so much fun…cant stop watching your videos

#### M Mm

(July 20, 2018 - 10:54 pm)

Hello Excel Fun, i'm watching lots of your video. Can you help me out this prob. in excel ; Time-in – 7:00 a.m ; and Time-out 8:00 a.m I tried your solution but it's not working.

#### Martrica Allumns

(July 20, 2018 - 10:54 pm)

I am trying to calculate the variance between the scheduled end time and actual end time. I am looking for a formula that calculates overnight (am-pm), am – am, and  pm -pm. The formula I am using is the mod formula which works perfectly until it attempts to calculate am – am. Then it gives me 23 hours. Example: Scheduled to clock out at 5:30 AM, and the actual punch out is 5:29 AM= 23:59. Here is my formula =IFERROR(MOD(TIMEVALUE(\$W8)-TIMEVALUE(\$O8),1),"") What formula can I use to calculate everything?

#### DRSteele

(July 20, 2018 - 10:54 pm)

Very interesting…but baffling. I wish MS would create a function to handle all this. You can vote for its creation here: http://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10730205-create-networkhours-as-a-new-function

#### Thomson Tam

(July 20, 2018 - 10:54 pm)

very useful video, good to learn new way and simple to work with Excel!

#### Ghazwan Elias

(July 20, 2018 - 10:54 pm)

thank a lot
I learned from your videos too much
thanks again

Eng. Ghazwan

#### Luis Roman

(July 20, 2018 - 10:54 pm)

Suggestion it will be helpful to understand how compute average days and average hours using rhe days oroportions. This video is an, excellent explanation and demonstration. Thanks for sharing.

#### Bill Szysz

(July 20, 2018 - 10:54 pm)

Thanks Mike 🙂
Great explanation how MOD works !!! (especially the part about the algorithm).

#### hamy72

(July 20, 2018 - 10:54 pm)

#### martin newton Manalang

(July 20, 2018 - 10:54 pm)

This is really great thank you 🙂

#### Derek Lowry

(July 20, 2018 - 10:54 pm)

great, thanks

#### krn14242

(July 20, 2018 - 10:54 pm)

Excel lent video. Love that MOD. Thanks Mike.

#### pmsocho

(July 20, 2018 - 10:54 pm)

Great!

SUMPRODUCT, INDEX, AGGREGATE, LOOKUP and what was the fifth function?

#### Hoppi Hopp

(July 20, 2018 - 10:54 pm)

This is really useful! Thanks a lot.

#### Don Davis

(July 20, 2018 - 10:54 pm)

Excellent, I will definitely be using the Mod from now on. Thank you for showing us this great short cut

#### buddymartin71

(July 20, 2018 - 10:54 pm)

That is really cool! I hope I find some way I can apply that to stuff I use excel for.

#### Jeff Lawrence Balasbas

(July 20, 2018 - 10:54 pm)

Thank you! 🙂

#### ExcelIsFun

(July 20, 2018 - 10:54 pm)

Excel Magic Trick 1206: Day & Night Shift Time Calculations: Add Total Hours Single Cell Formula