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




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….

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

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

Leave a Reply

Your email address will not be published. Required fields are marked *