 Excel Timesheets Add & Convert General Knowledge Part A. Create a basic Excel Timesheet Add up Hours in Excel How does excel calculate hours Part B. Insert Unpaid Lunch Breaks Convert Conventional Hours into a Decimal # How does excel calculate dates Part C. Calculate Overtime Pay Convert Hours and Minutes into Minutes Express Log in and Log out in Excel Timesheets for Night Shifts Average Hours ignoring Zero's and Error Values Entering only the last 2 digits of a year

Timesheets for Night-shiftsExcel Timesheets to calculate Nightshifts.

Excel cannot handle negative times.  If you start working at 11:00 pm and finish at 7:00 am, Excel doesn't understand it's a new day.

Here are 3 methods on how to explain Excel that you finished working at 7:00 am of the following day.

Method A:

Enter the full date and time you logged in and logged out.

In cell A1, enter:            6/1/2008  11:00:00 PM
In cell B1 enter:            6/2/2008  7:00:00 AM

In cell C1, enter:        =SUM(B1-A1)*24
Format as number

Method B:

In cell C1, enter:

=(IF(A1>B1,B1+1-A1,B1-A1))*24

Format the cell to "Number".

You are telling excel:

If A1 (11:00 pm) is bigger than B1 (7:00 am), than add one day to B1 (7:00 am), and only after that subtract it from 11:00 pm.
However, if that's not the case, than simply do B1 - A1
When you are done, multiply my total by 24.

Method C:

=(B1-A1+(B1<A1))*24

You are telling excel to:

"Subtract B1 from A1",  plus:

"B1 (7:00 am) is smaller than A1 (11:00 pm)".  Is it true?
If it's true or One, add 1 (one day)
If it's False or Zero, add zero.

Multiple my total by 24.

Think Different, think Night shifts.   