# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] Employees working past midnight - Time sheet dilemma

## MacHead

This website has been so helpful. I am devouring the archives. I have a question though that I don't think has been answered before.

Our workplace is using google sheets to let our union employees clock in and clock out remotely. Our union workers have many special rules/guidelines for tracking time that I incorporated into our original timesheet.  Life was good. BUT.... now they are working past midnight and google sheets is getting confused. I tried to add multiple lines per day and have our employees enter data like this: If working 6:30pm-4am, enter 6:30pm-12am on line 1, 12am-4am on line 2. 

Here is the spreadsheet. I have an example (Monday) on the page that works perfectly. When i copy and paste that example into other cells, those cells compute correctly too. But when i then clear the cells and try to enter new data that crosses past midnight, I get the #### error. 

Any reason why there is one magical set of numbers that work and nothing else does? For convenience, I turned all the 'invisible' formula garble to a visible grey color on the right side of the timesheet.

https://docs.google.com/a/frymanmana...it?usp=sharing

thank you.

----------


## JBeaucaire

Unable to access that link.  Can you post an Excel version of the problematic sample data/formulas?

----------


## MacHead

Ah! I had the share settings wrong. It works now! here is the link again:

https://docs.google.com/spreadsheets...it?usp=sharing

----------


## JBeaucaire

Which cell exactly? 

In general, if I had two cells with IN and OUT time punches

A1:  6:30 PM
B1:  2:30 AM

The formula I would use to calculate the hours for that shift would simply be:

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

----------


## MacHead

I need column F to correctly calculate the total hours. Right now, I have clock out-clock in being done with this formula: =(HOUR(E15-C15)+(MINUTE(E15-C15)/60))-D15 but that doesn't account for shifts past midnight.

----------


## JBeaucaire

The formula in post #4 is still the correct answer.

C15: 6:30 AM
D15: 1              (break)
E15: 2:30 AM

F15: *=(((E15-C15)+(C15>E15))*24)-D15*

----------


## Richard Buttrey

Hi,

In such circumstances I generally add an IF test to the basic formula that compares the time out with the time in and if it's less then the time in add 24 hours otherwise zero.

----------


## JBeaucaire

Richard, that is what the part in blue in post #6 does.

----------


## MacHead

Thank you both. I am going to slowly digest what each of you proposed so that I can understand why the two formulas achieve the same end result. 

I can never reciprocate with similar excel help, but I will gladly edit up to 1,000 words of text either of you have. I used to work in the writing lab in graduate school and do freelance editing when I'm not at work stumbling through excel/google spreadsheets.

----------


## JBeaucaire

In case it's  not  obvious to you Excel "time" numerics are all fractions of 1.  

6:00 am = .25
12 noon = .5
6:00 pm = .75
Midnight = 0

So when you subtract an OUT time from an IN time you typically just get another fraction.    Here's an example... row 1 shows the "time" display, row 3 shows the same time values formatted as "General" so you can see the decimal values.
http://screencast.com/t/wZGNklmFCr

Now, when you subtract an OUT that is a lower number than the IN, this is caused by a shift passing midnight.  A basic  B1-A1 won't work in this instance because the result is a negative number and all time values have to be positive.  You can fix this by using a BOOLEAN test, a simple construct that gives a TRUE/FALSE result.

(A1>B1)

When you slip this into a formula the TRUE result gets turned into a 1 and added to the result, turning a broken negative time result into an accurate time result going over midnight.

If a shift completes before midnight then (A1>B1) results in FALSE which gets turned into a 0, so nothing gets added and the result is still correct.

Lastly, multiplying your result by 24 turns your time value into a decimal, format the cell as "General" and you get the correct answer.

----------


## JBeaucaire

If that takes care of your original question, please select _Thread Tools_ from the menu link above and mark this thread as SOLVED.  Thanks.

----------


## MacHead

I had no idea! Thank you.

----------

