Excel Magic Trick 501: Excel Time Format & Calculations (10 Examples)

Learn about:
1.What time is in Excel
2.Time in Excel is a decimal between 0 and 1, and the decimal represents the proportion of one 24 hour day that has elapsed
3.Time Number Format sits on top of the decimal
4.The decimal is used in time calculation formulas, not the formatted number
5.Payroll time Calculation
6.Day Shift Time Worked Calculation: End Time Start Time
7.Night Shift Time Worked Calculation: MOD function
8.Round Time to the nearest 5 minutes with ROUND or MROUND functions
9.Add Time above 24 hours using custom Number formatting
10.Add Time above 24 hours using formula and the number 24 and General Number Format

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• Time
• Formatting
• Calculation
• Formula
• Hours
• Worked
• Day
• Night
• Shift
• MOD
• ROUNF
• MROUND
• function
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[poerc]

Thank you for the awesome instructions! I admire anyone who can clearly get their point across.

[ExcelIsFun]

You are welcome!

[MrNyc1992]

=IF(ISERROR(IF(ISBLANK(A1),"",­IF(ISBLANK(B1),"",ROUNDUP(MOD(­ROUND((TEXT(B1,"00\:00")-TEXT(­A1,"00\:00"))*1440,0)/1440,1)*­24,1)))),"",(IF(ISBLANK(A1),""­,IF(ISBLANK(B1),"",ROUNDUP(MOD­(ROUND((TEXT(B1,"00\:00")-TEXT­(A1,"00\:00"))*1440,0)/1440,1)­*24,1)))))

this is the formulas that i am working with right how.

[ExcelIsFun]

I am currently working 80+ hours week. No time. Try posting to THE best Excel question site:

mrexcel[dot]com/forum

[jasoncolman2002]

sorry how do i download the examples so i can work on the excel sheet?

[ExcelIsFun]

To learn how to download workbooks, search for and watch this video title:

excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks

[KHOLOUD911]

Brilliant !! very clear and did the job .. Keep going :)

[MrNyc1992]

HI i have a question for you. How do you get one tenths of hour every six min. here is any exmaple: 1400 2202 8.1

you every six minuets is one tenth of hour. Right how i have it round up after every one or two minuets.

[ExcelIsFun]

You are welcome!!!

[seal2bkl]

You are the man, great teacher, I wish I had someone like you for any of my classes in school, obviously in math or computers would have been the most helpful. Thanks!