Calculating over 24 hrs 1

[crmorgan]

I need help calculating time. I have a spreadsheet that calculates hours worked for employees. One column has clock-in time and the next column has clock-out time. The problem comes in when I have employees clock out on the next day. For example, he clocks in at 23:00 and clocks out at 07:00. How can I create a formula to recognize that the date has changed and give me an accurate subtraction?

 

[JPaules]

Calculating time in Excel can be tricky, especially when you're dealing with someone who works third shift.

http://articles.techrepublic.com.com/5100-10878_11-5756139.html

http://en.allexperts.com/q/Excel-1059/Calculating-difference-times.htm

--
JP

 

[GSCaupling]

Hi crmorgan,

See also:

[blue]faq68-5827[/blue]

Question: Where/how are the dates recorded on your spreadsheet?

GS

[red]******^*******
[small]I[/small] [small]Love[/small] [♥] [small]Redundancy.[/small][/red]

 

[TonyJollans]

I'm sure the links provided are helpful but this is really a non-issue. If you have times that include dates (as you suggest), just subtract the earlier from the later regardless of whether they go over midnight and you should get the right answer.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

FYI,

There is really a distinction that ought to be made between Date/Time, which is a point in time, and Duration, which is the difference between two Date/Time values, and can be expressed in terms of millinea, centuries, years, days, hours, minutes, seconds or any combination thereof.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GSCaupling]

Tony,

I agree that the difference between two date/time figures would give an accurate answer. However, I would be surprised if crmorgan is recording the date portion with each time entry. That's why I asked where/how the dates are entered.

He probably has the date recorded separately (as a header, perhaps) from the time, in which case he would need to combine them before doing the calculations.

GS

[red]******^*******
[small]I[/small] [small]Love[/small] [♥] [small]Redundancy.[/small][/red]

 

[AnotherHiggins]

I'm with GS in thinking that the cell only contains a time, not a date & time.

But assuming that no one works a shift over 24 hours, then you can use the logic that If(EndTime < StartTime, 1+EndTime - StartTime, EndTime - StartTime)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

But assuming that no one works a shift over 24 hours...

But what about the shiftless?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Oh, you just fire them.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Heartless!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Fire them, too!

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Scrooged again!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GSCaupling]

Nice, John. At first glance I didn't think your solution would work for shifts over 12 hours, but a little testing eased my simple mind... it works perfectly.

Have a star (for the solution, not for being a heartless b...).

GS

[red]******^*******
[small]I[/small] [small]Love[/small] [&hearts;] [small]Redundancy.[/small][/red]