### About this tutorial:

**Video duration: **10:35

Download File:

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

please con you make videos about ( Project 2013 )

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)Thanks Mike… Just a formula that I wanting to learn more about…!

## 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

Download File: http://people.highline.edu/mgirvin/excelisfun.htm

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. (06:50) Single Cell Array Formula for adding total hours for week using MOD function, Array operations and SUMPRODUCT function