XL 2010: Time on the Clock 4pm to 1am 1

[ladyck3]

I have been maintaining a spreadsheet for YEARS with hours of operation anywhere from 7:00 AM to 11:00 PM with no problems...

Subtract login time from log out time and there you have it.

(Skip if you answer this, don't tell me the ABS is the solution AGAIN ...)

So if I have someone log in at 4pm and work til 1am how do I figure out how many [h]:mm:ss the individual was logged in. I can't subtrace 4 from 1... is there somehow you have to factor in the 24 hr period in order for the calculation to realize that its spanning to the next day? (After midnight?)

I'm lost, ONCE AGAIN.

Thanks in advance...

ladyck3
aka: Laurie

 

[SkipVought]

hi,

You subtract the Date/Time values the same way or if you are just using Time values, you add 1 to time values after 12:00 am before calculating.

Skip,

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

 

[SkipVought]

You could use a formula like this
[tt]
=IF(End>Start,0,1)+End-Start
[/tt]

Skip,

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

 

[ladyck3]

I will try the calculation in the morning.
I left it at work... did not want to get frustrated overnight

Thank you will be back

ladyck3
aka: Laurie

 

[AzizKamal]

If you enter both Date and Time, Excel will figure out that it is spanning to the next day.

Cell A1: 4/23/2013 4:00 PM
Cell Format: m/d/yyyy h:mm AM/PM

Cell B1: 4/24/2013 1:00 AM
Cell Format: m/d/yyyy h:mm AM/PM

Cell C1: =B1-A1
Cell Format: [h]:mm:ss

 

[ladyck3]

I'm back and today is the day that I most definitely need this to work, so it has all of my focus until I get this fixed.
AND>.. GUESS WHAT?

After several trials and errors with the two reocmmendations... SKIP WON

Here is the ultimate answer.
O119 is the actual login time
P119 is the actual log out time
Then I need to subtract and hour and 30 minutes for lunch and also two 15 minute breaks.
On the clock there should be 9 hrs less 1:30 for a total working time of 7:30 h:mm

IT WORKED:
Data: Row 119, cells O, P, Q and R

Login (O119) 16:00:57
Log Out (P119) 1:00:46

Formula in Q119
=IF(P119>O119,0,1)+(P119-O119)-TIME(1,30,0)

Response (Q119 and R119):
Work Time (Q119) 7:29:49
Less than 7:30(R119 indicates less than the required 7:30) 0:00:11

PERFECTION.... once again, you are my answer...
The answer to everything Excel is NOT "42" its "SKIP"
MY HEEEEROW

AZIZ,
Your formula returned errors and I did format the cells as recommended.
I do have a date column and then the login and out times in 2 other columns... but I formatted each of the login and out cells to read mm/dd/yyyy h:mm AM/PM ... it returned errors each time.
It sounds like a perfect solution but for me, (probably stupid user trick) it just did not work. And frankly, I really did not need to show the dates again, too much visual information.

I do appreciate the recommendation however. Thanks so much





ladyck3
aka: Laurie

 

[SkipVought]

A    B
     [B][HIGHLIGHT]=INT(SUM(B2:B12)/7)-2[/HIGHLIGHT][/B]
S    =CODE(A2)
K    =CODE(A3)
I    =CODE(A4)
P    =CODE(A5)

Skip,

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

 

[ladyck3]

Ya lost me there buddy....

ladyck3
aka: Laurie

 

[SkipVought]

B1 returns [highlight]42[/highlight]!!! ;-)

Skip,

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

 

[ladyck3]

I figured it was one of your tricky excel tricks of humor but I'm not quite that savvy
You make me smile/laugh

Sometimes, like now... even roll my eyes

You're gonna miss me when I'm gone...

ladyck3
aka: Laurie

 

[SkipVought]

Gone? When's that gonna be?

Skip,

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

 

[ladyck3]

Retiring on May 31...
Husband asked... WHAT are YOU going to do w/o EXCEL??????????? (I live in it daily) and I said don't worry... I'll think of SOMETHING... so I may pop in from time to time.

Now that I will have no need for it, I'll have time to maybe learn VBA LOL... go figure.
Until then I have too many how to guides to write for the 30 or so tasks that I do... which change daily, so I get it done and I'm editing it all the next... <sigh>

I'm asked continually... how many days?
I answer... I have NO EARTHLY IDEA... I don't have time to count or to put in a formula which will tell me each morning...
Besides its dwindling and I don't wanna know the actual count... no time to get it all done.. I'm freaking enough as it is.. Hopefully they'll get someone who knows Excel (hopefully better than I do) before I'm gone

<shrug> We'll see

ladyck3
aka: Laurie

 

[SkipVought]

I'll be a year and a month behind you!

Skip,

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

 

[ladyck3]

Then I best start all of my projects on June 1st so you are here when I need you
Or, like me, is Excel in your blood and you may not be able to stay away?

You are a true guru, I do not come close to holding the title.. if I was able to assist somewhat like you do, I'd probably stick around here.... rather than just flip the switch and lights out

Then again you'll have time for boating, fishing, flying, traveling, sitting around doing nothing... and may just give it all up... flipping the switch

COMPLETELY Understood! LOL

ladyck3
aka: Laurie

 

[AzizKamal]

Thanks Laurie.

Glad you found the solution.

I tried your example:

Entered in Cell O119:
4/23/2013 16:00:57

Entered in Cell P119:
4/24/2013 1:00:46

Entered in Cell Q119:
=(P119-O119)-TIME(1,30,0)

Value in Cell Q119 appeared:
7:29:49

You have date and time in separate columns. In this case, I agree that Skip's solution is more appropriate.

 

[SkipVought]

I will be lurking around Tek-Tips for some time to come. It is in my blood!

Skip,

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

 

[ladyck3]

Aziz,
Thanks for reviewing this with me and verifying your method was not appropriate for my task at hand. It would have nagged me....why the heck can't *I* get this to work. Appreciate your time.

And to Skip...
I KNEW IT, I KNEW IT, I KNEW IT...you lil lurker. Hehehe

And I totally get it. Now I will have a chance to start lurking and learn more!




ladyck3
aka: Laurie

 

[byundt]

I realize that the question has already been answered, but a really succinct formula for hours worked uses the MOD function:
=MOD(Finish - Start,1)

The formula assumes that you have entered just a time in the Finish and Start cells. It works even if the Start is on one day and the Finish on the next--as long as you don't work 24 hours or more.