Adding / Subtracting TIME VAULES / UNITS (Not time of Day) - I'm lost ... [RESOLVED]

[BaLLZaCH]

.
[RESOLVED]
(Thanks to JBeauacire)




Hey all ! I have been away for a long, long time.

I have a little simple sheet that is kicking my @$$ ! I cannot figure out hour to get this ONE value of time ...

--------------------------

I want to take my total time (already calculated), and subtract 12 hours.

If the total value is zero or less, then nevermind. BUT if it is greater than zero, then display the difference....

( Example: 12:30, minus 12:00, = :30 )

--------------------------




Problem is, I discovered, is Excel time doesn't just work like that. It keeps thinking I want to subtract times of the day, giving me another time of the day...

After long searching I converted the sheet to 1904 time and still no luck. (In order to try different formulas and try negatives)...

I have tried changing the formula 102'ish different ways to achieve the same thing, but all the same wrong'ish results.




I have attached a sample sheet for viewing. (I am trying to change the CONTINUOUS TIME cell (A5) , which works correctly SOMETIMES depending on what start/stop times are entered)

***As Example, the sheet shows a clock out time of 12:05. And correctly displays the overage as :35 minutes. BUT, just try and change the clock out (tie up) time to, say, 11:00 , and everything goes wrong.




I KNOW it has to be possible, I just cant figure it out !


Thanks everyone !

[JBeaucaire]

The formulas I recommend are:

A4: =(A2-A1)+(A1>A2)
A5: =MAX(0, H4-TIMEVALUE("12:00 PM"))

[BaLLZaCH]

Wow ..... OK ,,,

So I know you meant "A4" not "H4", and... It worked perfect....


I have never used the "MAX" option ,,,, didn't know it existed, I don't even know what to say but thank you!

So, This is resolved.... !!! You nailed it with the TIMEVALUE, and thanks for the lesson on MAX!!!

Thank you so much JB !!!
+1 on Rep !

[RESOLVED]

-------------------------------------------------------------


EDIT:
EXTRA NOTES :

***NOTES: To anyone who found this page searching for a similar issue, as JB pointed out, you can use the - (or + or whatever) TIMEVALUE(xx:xx) and you don't specifically have to have the AM or PM afterwards, it just so happens works in this case but you can leave off the AM/PM if needed.

IF you needed to change the sheet to 1904 filesystem (I found during searching that PCs use 1900 and MACs use 1904 as default.... for compatibility reasons, or, the reason I found it was for NEGEATIVE values (because otherwise you get errors with 1900 and neg.), so if you change to 1904 you CAN get negatives (Do your own research).... I don't need negs. anymore, just interesting info... - But to change it, go to TOP LEFT OF EXCEL, click the |-> "DOWN ARROW", click MORE COMMANDS. Click ADVACNED, scroll down to "When Calculating This Workbook" , click the "1904" checkbox, and save.

And as a side note, I never used MAX formula. Thank you JB for pointing that out. It basically prevents an extra line of code that says "if less than xxx then xxx or xxx" ... so in his example, if a-b=less than zero, nothing .... But if more than zero, then display result. Thanks again.

I just wanted to add side-notes in case somebody ran across this searching, and I hope it may help.

Thank you to EXCELFORUM members !!!


Again, [RESOLVED], and thank you.

[JBeaucaire]

Glad to help. For your convenience you can select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.