Excel Formula Subtracting Time Returns ############ 1

[ladyck3]

I have a spreadsheet that is configured to show adherence or point for late login or early log out.

The forumla also subtracts an hour and a half for Lunch and breaks (-5400 seconds).

See the formula below:

=IF(ISERROR(SUM((W124*86400)-(V124*86400)-5400)/86400),"",(SUM((W124*86400)-(V124*86400)-5400)/86400))

I'm using the =IF(ISERROR function so that, when the login time (V124) and the log out time (W124) are emtpy, the formula can reside in X124 but appear empty.

X is the "time worked" 9 hrs - 1.5 hrs (lunch and break) so it should show 07:30:00 +

However, it does not work... it shows all ################ no matter what I try.

I realize I am subtracting 5400 seconds from nothing so its a negative number without any data in V and W. I'm handing this off to a coworker while I'm on vacation and rather than having him have to copy and paste the forumla in the X cell daily, I'd sure like to make it automatic for him. He's not yet real Excel savvy (and I know enough to be dangerous but they think ME... a guru... pfft if they only knew) and whatever I can do to make this as easy peasy as possible, the better.

I am confident that using some sort of VBA can cover this but I'm not one bit savvy on VBA so I'm hoping there is a function or formula of SOME SORT that I might consider.

I've been using this formula for about a year now but never had anyone else in my workbook... I'm afraid of this thing blowing up.

I doubt it matters but these cells (V and W) are conditionally formatted to show green if within the threshold and red when not.

Additionally I'm using T and U to show adherence points, not that this should matter for formatting X to show blank when no time values are entered into V and W.

Have I lost you yet? If not, any advice or direction?

Thanks group.. you are always so helpful and I've learned so much... Like the =IF(ERROR thing... what a GODSEND

Thank you!

Laurie

OH and one more thing... in Excel, when chosing sell formatting, I have no clue what or where to find answers... what those different formats are under Custom, the ones like:

#,##0
$#,##0);[Red]($#,##0)
# ??/??
##0.0E+o

or 0.00E+00 and those sort of things...

or

_($* #,##0_);_($*(#,##0);_$*"-"_;_(@_)

.... Just to mention a few..

what in the world is all of this? I know its not timem related and tht's what I'm working with right now but I've always been curious.

Heck there's just an @ symbol in there <shrug> WHY?

Thanks again....

LadyCK3
aka: Laurie

 

[SkipVought]

Excel TIME values have units of DAYS. What are you doing with SECONDS?

If you have 2 time values, tStart & tEnd, the difference, which is a duration, including 1.5 hours for lunch is...
[tt]
=tEnd-tStart-TIME(1,30,0)
[/tt]


Skip,

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

 

[ladyck3]

OH MY WORD... Skip I swear to all that is Excel.. YOU are my HEEEERRRROOOO

http://WWW :)

I was using seconds, because that is how I know to deal with time... I told ya, they "THINK" I'm a guru... LOL..that's hysterical, I swear

Okie...let me run with this lil jewel

But the problem persists with the ############# if I enter the forumla into the result cell, but there is data in the start/end fields (awaiting the next day's data) the 'blank' cell is not blank... and IF(ISERROR does not work...

I'm sure its the fact that with no start/end data, the result is negative an hour and a half and the result cell doesn't like that.

I LOVE THIS FORMULA! Its SO SIMPLE!




LadyCK3
aka: Laurie

 

[SkipVought]

1. What are the two values?

2. Where are they located?

3. What is your formula?

The ############## means a negative value.

The solution is DON'T enter the formula in a row that does not have COMPLETE and VALID data. This can be accomplished using a Table or List, depending on your version of Excel.

Skip,

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

 

[ladyck3]

Well I knew THAT "Just don't put the forumla in there" that's like.. Doc it hurts when I do this.. Doc says... well don't do that. Thanks buddy hehehehehehehe

OOOOOoo the table thingy again hmmm, I know how that works for one scenario, how does it work here because its calculations (I love learning Excel)

Chart #1
Login Is column Q
Logout column R

Chart #2
Login is column V
Logout is column W

The login and out times vary by individual.
I have an adherence section that tallies points for late login and early departure using your table method

Remember this thread?
Nested IF Statement using Time Variables
thread68-1585826

Now the title does not reflect the solution which was AWESOME.. I'm assuming you are talking something similar but these functions are a learn as I go kind of thing.

Thanks!




LadyCK3
aka: Laurie

 

[SkipVought]

Okay. So you have data in a row that is 'imcomplete.'

Test for the value that might not be there, like the tEnd value in the row...
[tt]
=If(ISBLANK(tEnd),"",tEnd-tStart)
[/tt]
or this...
[tt]
=IF(ISBLANK(tEnd),TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())),tEnd)-tStart
[/tt]
which calculates the duration from start until NOW()

With your approch, I hope you will never have time values that span midnight.


Skip,

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

 

[SkipVought]

or a shorter, less obvious version of the second formula...
[tt]
=IF(ISBLANK(tEnd),NOW()-INT(NOW()),tEnd)-tStart
[/tt]

Skip,

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

 

[ladyck3]

Doing either

=IF(ISBLANK(tEnd),TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())),tEnd)-tStart

or

=IF(ISBLANK(tEnd),NOW()-INT(NOW()),tEnd)-tStart


Is returning a value of 12:27:43
I guess maybe I was not clear... the spreadsheet has no values in either the start or end until the data is entered, all at the same time.

A report is run in the morning and then the values are pasted into the report for the results.

So while the spreadsheet is waiting for data I'm hoping to have the formula (whatever it ends up being) in the cell ready to calculate when the data is added.

This shows a result even if no data is present because its reporting "NOW".

HOWEVER.....

=If(ISBLANK(tEnd),"",tEnd-tStart)

IS THE WINNER!!! This is exactly what I'm looking for, it resolves the issue. So it ends up the IF(ISERROR should really be, in this case IF(ISBLANK

Going into my lil help file, (the one titled SKIP_IS_THE_MAN)

Isn't it ridiculous how they think I'm a guru when this is so simple, I even 'get it' but its not something I've used before so I had no idea 'to' use it.

Thanks so much! <may a shower of stars descend upon you>

RESOLVED! (and not to my knowledge on the midnight thing.. not for the records that *I* have to keep)


LadyCK3
aka: Laurie

 

[ladyck3]

OOPS PUTTING ON THE BRAKES!

I'll play with the TIME thingy you showed me earlier in this post because this still does not subtract the hour and a half for lunch and breaks

Back to the construction zone

And VOILA <I just know you will have a better way> but this does work


=IF(ISBLANK(R124),"",(R124-Q124)-TIME(1,30,0))



LadyCK3
aka: Laurie

 

[SkipVought]

So while the spreadsheet is waiting for data I'm hoping to have the formula (whatever it ends up being) in the cell ready to calculate when the data is added.

I mistakenly thought that you had the START but not the END.

If you have NEITHER, then don't have ANYTHING in the row until you put data in the row. Again, using the Table or List feature, lets you add rows as you have data WITHOUT having to PRE-ENTER formulas or COPY formulas to new rows of data.

Skip,

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

 

[ladyck3]

Well I'm not sure how to do that with this different data but I will take a look at it as soon as I can (probably next month, seriously). I am preparing for a 2.5 week vacation that I will TOTALLY PAY FOR when I get back, I'll be SO BEHIND.

I'm going to take a look at this when time permits before 8/20 so plan on hearing back

I do so appreciate you



LadyCK3
aka: Laurie