Excel 2010 - Time Calculation 1

[ladyck3]

I am to show lost time on a login/logout report.
An agent on a normal day is on the clock for 9 hours, but 1,30,00 is subtracted for breaks and lunch so actual worktime is 7 hrs and 30 minutes each day.
If they log in or out late/early there is lost time involved.

I have a set cell E3 that holds the default value of 7:30:00 now if I subtract the actual time that may be less than 7 hrs and 30 minutes it does fine.
But if the time is the same it will put zeros, fine again, but if its over... it errors out with ####### when I that, I just delete it because over is ok, we're tracking lost time only.

Lost time in any one day is less than an hour if its over, it is more than likely excused and will not be counted..so I'd delete it that's why the ##### are not an issue.

The problem is totals...it is totaled by week... so if we have a week that is 0:56:13, 0:20:00, 0:00:00 and 0:34:05 the total would be 1:50:18 again this is accurate....

So I can have the format as h:mm:ss

But if the total is for instance, 0:56:00 it shows up in the total if using h:mm:ss as 2:56:00
How/why is this happening?

Its probably me

I need this to be uniform and not have to go in and format the cell every time there is some sort of inconsistency.
All of the cell format options under CUSTOM are not understood by me, nor do I know where to find an explanation of the options in that window. Can someone please tell me where I am going wrong or educate me on what I have yet to learn?

Thank you.


ladyck3
aka: Laurie

 

[SkipVought]

Laurie,

#### you will see in Excel Help, is what happens when a Date/Time formatted cell contains an OUT-OF-BOUNDS value.

I'm afraid that your example is not much of an example. It's like saying, "I get 1 and I get 2." We need to see the values that sum to the value you are getting. Simply changing the format does not change a single thing in the underlying value. As I state in faq68-5827, [red]Formatting does not change ANYTHING!!!
[/red]

Skip,

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

 

[ladyck3]

Well info on the formatting is good.... I will review the link when I get home... time for me to scoot for the day

<hi Skip> I appreciate the pointing in the right direction, and I knew the ### was cuz it could not calculate it properly the term Out of Bounds is what I was saying only with wrong terms

ladyck3
aka: Laurie

 

[ladyck3]

Having read your article, I believe I might understand howto do what I need.

Static Cell (required hours an dminutes of working time) - Actual time worked...(cell L6)

E2 = 0:07:30 (Required h:mm) =(E2*86400)-(L6*86400) Format the results as mm:ss

Then I can do my subtotals by week and totals by month, etc.

Am Ion the right track?
Idon't have it with me to test it... will do so tomorrow morning.

I think I'm right here (crossing fingers>

ladyck3
aka: Laurie

 

[SkipVought]

Laurie,

WHY are you converting your Date/Time values to SECONDS???

Skip,

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

 

[SkipVought]

You never fully explain exactly what you are doing. A Login/Logout report, presumably has Date/Time values for each transaction. So a simple subtraction will calculate each DURATION of time that an employee was IN...
[tt]
DURATION
=Logout - Login
[/tt]
This DURATION, I would format as [hh]:mm:ss.

UNITS is DAYS. I'd assume that your FINAL result might be HOURS. (24 HRS per DAY)

But maybe I'm out in left field.

Skip,

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

 

[SkipVought]

Here is my In/Out table that I have made a Structured Table, named tInOut, with TOTALS

[b]
login             logout	    duration[/b]
12/14/2012 7:01   12/14/2012 13:48  6:47:00
12/14/2012 14:51  12/14/2012 15:55  1:04:00[b][highlight]
Total                               7:51:00[/highlight][/b]

Where the duration formula is
[tt]
=tInOut[[#This Row],[logout]]-tInOut[[#This Row],[login]]
[/tt]
Then here's the FINAL calculation for HOURS DURATION
[tt]
=(tInOut[[#Totals],[duration]]-TIME(1,30,0))*24
[/tt]


Skip,

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

 

[ladyck3]

Meetings Meetings... sorry I have not contacted you earlier... and I have not had a chance to test my theory.
Here is what I am trying to do....
I currently enter

1. Data Input: Login time in one cell -------- Logout time in the adjacent cell no date is involved just h:mm:ss
2. Calculated Time "on the clock": In the cell adacent is the time worked (logout - login - 1.5 hrs) to get the total time on the clock.
3. Expected Work Time Daily (less 1.5 hrs for lunch/break): Total time should be 7 hrs and 30 minutes each day.

I have a static cell in the upper right (cell D3) with that expected time 7:30:00

What I am trying to determine is if the result of #2 is less than the time for #3, what is the differential if negative or less than #3 to determine "Lost Time". Well I guess I could have lost/gained time and just conditional formatting to red if negative and greeen if ontime or over.

This time is then added on a
A. Weekly basis (Mon-Friday)
B. Monthly
C. Quarterly
D. Half Year
E. End of year, total lost time.

I have all of the above working except #2 on how to get the actual minutes lost.
BUT, I'm thinking that if I convert the data (in my calculation) to seconds, the results in seconds can then be /60 to get the Minutes/Seconds. Then the subsequent totals weekly, monthly, etc.. should calculate accurately.

It may be more steps than you may know what to do because you are far more educated than I, when it comes to Excel but this is what *I* understand and can handle should something blow up. If this will results in accurate information.

It will be another 30 to 45 minutes before I can test this.

OR stear me in another direction...
You tell me

As ALWAYS... thanks for your patience!!!!





ladyck3
aka: Laurie

 

[SkipVought]

1. Data Input: Login time in one cell -------- Logout time in the adjacent cell no date is involved just h:mm:ss

No Date works ONLY when the In and Out are on the same day.

#2
[tt]
=if(logout-login<$E$2,logout-login,logout-login-$E$2)
[/tt]


Minutes lost is just the duration in DAYS * 24 Hrs per DAY * 60 Min per Hr.

See how that works?

DAYS * Hrs * Min
----------------
       Day   Hr

Days/Day cancels out units and Hrs/Hr cancels out units leaving MINUTES!

Skip,

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

 

[ladyck3]

I was trying to figure out how to upload an image but I don't have a place online to provide a URL and it does not allow for attaching a desktop file.

In $E$3
7:30:00

In cells L288:M292 I have the following:
Using what I understand the formula to be: =IF(N288<$E$3,N288,N288-$E$3)*60
L | M | N | O
-------------------------------------------------------------------
Login | Log Out | Work Time | Differential (format: [h]:mm:ss
-----------------------------------------------------------------
11:00:12 | 20:01:37 | 7:31:25 | 1:25:00 | actually 1 minute 25 seconds OVER
10:59:30 | 20:01:49 | 7:32:19 | 2:19:00 | 2 minutes 19 seconds over
11:00:05 | 20:00:32 | 7:30:27 | 0:27:00 | 27 seconds over
11:01:20 | 20:00:39 | 7:29:19 | 449:19:00 | This is the only negative.. .what the heck happened HERE??
11:00:00 | 20:00:30 | 7:30:30 | 0:30:00 | 30 seconds over

I seriously need just the negatives and add them.

ladyck3
aka: Laurie

 

[SkipVought]

what is your formula in column N ( Work Time)?

Skip,

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

 

[SkipVought]

the NEGATIVE was 49 SESONDS UNDER

Your Min:Secs column over/under needs to be
[tt]
=abs(N288-$E$3)
[/tt]
for instance, and then another column for +/- for show over or under.

Skip,

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

 

[ladyck3]

I don't know abs, never used it (want to learn)
Formula in N (where the time shows as 499:19:00 for example) and this the same for in N

=IF(ISBLANK(M291),"",(M291-L291)-TIME(1,30,0))

M(log out) - L (Log in) - TIME for lunch/breaks.



ladyck3
aka: Laurie

 

[ladyck3]

I tried ABS and it worked perfectly except for one thing... it returns a value no matter what so I tried

=if(N288<$E$3),ABS(N288-$E$3),"")

it didn't like that (I know you are laughing and shaking your head, Laurie, Laurie, Laurie) I know

ladyck3
aka: Laurie

 

[ladyck3]

BUT................................

THIS WORKED: =IF(N291>$E$3,"",ABS(N291-$E$3))

ladyck3
aka: Laurie

 

[SkipVought]

Login     Log Out   Work Time  Differential (format: [h]:mm:ss  +/-
11:00:12  20:01:37  7:31:25    0:01:25                          +
10:59:30  20:01:49  7:32:19    0:02:19                          +
11:00:05  20:00:32  7:30:27    0:00:27                          +
11:01:20  20:00:39  7:29:19    0:00:41                          -
11:00:00  20:00:30  7:30:30    0:00:30                          +

my formula

Differential: =ABS(N288-$E$3)
+/-         : =IF(N288<$E$3,"-","+")

Skip,

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

 

[ladyck3]

Hmm, will try that later, logging out for the day....
Mine is working as is but only for negative numbers, does not take into account any overtime, but want to show losses, not sure if they want it to even out with any pluses on other days. <shrug> on that

RUNNING, gotta go NOW.
Thanks!

More after I try the suggestion posed.

ladyck3
aka: Laurie

 

[ladyck3]

I just wanted to thank you...
This [ =ABS(N288-$E$3) ]
Is working PERFECTLY
I'm using in conjunction with if its greater enter nothing [ =IF(N291>$E$3,"",ABS(N291-$E$3)) ]
But I'll dabble with the + / - today.

Skip, you are fantastic! Thanks so much

ladyck3
aka: Laurie

 

[lionelhill]

The other way to handle this is to turn on the option "Use 1904 date system". If you do so, negative time-values will miraculously display as a properly formatted time with a minus sign. But of course there may be other consequences.

The option is in the advanced group for Excel options in Excel 2007, and it's in Tools - Options - Calculation in Excel 2003. I have no idea where it is in Excel 2010 or other versions.

Here's Microsoft's help on the subject:

http://support.microsoft.com/kb/182247