Excel 2007 - Calculating Lost Time

[ladyck3]

I am trying to figure out lost time during a worker's day....
Lost time = 0:00:00 (Column AC) "Not Available"
+
Lost Time = 0:00:00 (Column AL) "Not Ready"

$V$4 = 1:30:00

This calculation works: =IF(ISBLANK(U102),TIME(0,0,0),SUM(AC102,AL102)-$V$4)

I'm trying to make sure that if there are no entries in the worksheet yet, the cell will remain blank until data is added so here's the breakdown (My thoughts)

If U102 is blank, enter 0:00:00, but if it is not empty enter the total Lost Time - 1:30:00 (Lunch and breaks) to provide complete and total lost time.

Problem is, some of the time personnel work through lunches so the result is negative after subtracting the 1:30:00 and the cell contains #################

SO, I'm trying to do an ISERROR statement and I'm lost...

=IF(ISERROR(ISBLANK(U99),TIME(0,0,0),SUM(AC99,AL99)-$V$4)<0,"0",(SUM(AC99,AL99)))

Can someone please help me think this through, I'm at my wit's end... now I'm getting a #VALUE! response.... and...I need it to be a formula not VB because if its VB and it breaks, I can't fix it ... I'm not knowledgeable about VB, syntax and the like.

HELP

LadyCK3
aka: Laurie

 

[Gruuuu]

What about just wrapping it in a MAX() function?

[tt]
MAX(0,formula)
[/tt]

Easy enough to monkey with if something breaks!

 

[ladyck3]

I used:

=MAX(0,IF(ISBLANK(U100),TIME(0,0,0),SUM(AC100,AL100)-$V$4))

This returned: 0:00:00 except it should have actually done the calculation and not subtract the 1:30:00 ... the actual total time then (usually because it is not a full shift) would be the sum of the two times period... a total in this case of 0:41:31.

You see there is no way to subtract 1:30:00 from 0:41:31

I'm sorry but I'm not familiar with using MAX for anything other than finding the maximum value in a range of cells....

(I keep TELLING THEM (here at work) that I am NOT the GURU you THINK that I am) They don't listen

But I bet I'm getting closer, right?

LadyCK3
aka: Laurie

 

[SkipVought]

Hi,

if there are no entries in the worksheet yet...

No need to enter formulas in rows in advance of data!!!

Use the TABLE feature of eXCEL 2007 -- REALLY NEAT!!! One of the BEST features of 2007 IMNSHO!

Insert Tab > Tables Group > Table Icon.

It will convert your table range into a 'TABLE'.

Any formula that you have in a column will be automatically propogated the the next row as you add data in that row.

Then you don't have to have that IF formula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

OMG NO NO NO....
This completely obliterated all of my conditional formatting...
I have data in the spreadsheet... I'm talking about a blank row... I enter data every day, a new line for each day of the month....

Nice feature I suppose, really HORRIBLE results for my needs .. I already have 3.5 months of data entered... I need to start this lost time thing in April and going forward....

Maybe I just need to manually carry it down from day to day rather than entering it down the entire column.

Skip, HIYA, good to see you, now NO MORE HEART ATTACKS Thank heavens for UNDO <catching breath> LOL


LadyCK3
aka: Laurie

 

[SkipVought]

This completely obliterated all of my conditional formatting

Are you SURE? I've done this with CF and they don't go away.

If you care to try this again, then activate the CF Icon and select Manage Rules...

At the TOP of the COnditional Formatting Rules Manager window is a Drop Down.

Select This Worksheet, and see if your rules are not still there.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

Well I'm on a deadline... I'll try that later Skip.

Right now what I've decided is that my formula works when it works... when it doesn't due to a shift variance such as a half day of work, I'll simply change the formula to add AC and AL and that's it.

Thank you Gruuuu, I'm using your MAX suggestion in the future rows and that should take care of it.. if not I'll just copy my formula on a daily basis.

I was thrown this project Tuesday and its a 10 page tool... this is stuff for just ONE entity... I need to move on

This is our Friday and I have to have it finished today

Happy Easter everyone!

Skip, you me and the greatest thing since Sliced bread "XL 2007 Tables"... I'll Be BAH-CK

<wink>


LadyCK3
aka: Laurie

 

[Markus4]

Hi Laurie,

when I have to work with time values I tenaciously stick to my rule:
calculate and display in separate cells.
This only because Excel still can not display negative time values.

Therefore:

if I cannot design the Excel sheets myself, maybe a user has to enter a time value in one cell. As a precaution I would convert it (with N(timevalue)) into a number. At this point all kinds of plausibility checks can be done. After that, subsequent calcuations must base only on this converted and checked value.

Intermediate and final results must be shown in separate cells. Example (assuming calculation result in C25):

=IF(C25<0;"- ";"")&TEXT(ABS(C25);"[h]:MM") *

Remember: further calculation must only base on C25. The cell shown above just displays a text. —

Furthermore:

If I can design the sheet myself, I'm inclined to never let the user enter time values (same with dates). Instead, he/she gets two cells to enter hour and minute value (five for date and time: year/month/day/hour/minute).

This approach definitely has its advantages: a) if user has to enter a time of day, you can easily check whether hour is between 0 and 23 and minute between 0 and 59, and b) converting those two entries is simple arithmetics. —

If the worksheet is about working times I tend to stay on the safe side and force the user to enter the date as well. If you use the above scheme (three cells for year, month, day) you won't nag the user because a) entering data is still easy, and b) some cells can be preallocated if you wish total comfort.

HTH.

Markus
_________________
* This is German, I don't know the US conventions: maybe you'll have to substitute ; with ,

 

[lionelhill]

There's nothing whatsoever to stop you from subtracting a large time from a smaller time and producing a negative result. Honestly, it works. You just can't display it (except see below).

That means you can carry out your calculations, and test if the result is negative. If it is negative, you can use a different calculation that would come out positive, or if you wish to display a negative time:
=IF(A1<B1, "-"&TEXT(ABS(A1-B1), "h:mm:ss"), A1-B1)