Working with time in Excel 2

[alphil2001]

I have a spreadsheet in excel that calculates the time and records working hours of a staff member. I have a calculation that adds up the time for the week but when I try and take the flexy time away from the time worked it sometimes comes up with a minus. This is what I want it to do but it only works when the time is in credit, it wont work when the time is in defecite and comes up with an error message. Is there a solution were I can subtract the flexy time and still be able to show time as a deficit.

Thanks
Alphil2001

 

[TonyJollans]

Hi alphil2001,

Excel cannot work with negative times at all. if you just want to show something which LOOKS LIKE a negative time, you could do smething like this:

A1: Worked Time
B1: Flexy Time

C1: =IF(A1>B1,A1-B1,TEXT(ABS(A1-B1),"-hh:mm&quot)

This will produce TEXT so the result cannot be used for further calculations. If you want to use it for calculations you will need to do something different; post back if that's what you want.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[xlbo]

negative times aren't really supported in excel
You can use this workaround - where times are in A1 & B1 but where it is a negative, be aware that it is transformed into TEXT and therefore you will need to convert it back to a date/time before you can do any more calculations with it:

=IF(A1-B1<0,&quot;-&quot;&TEXT(B1-A1,&quot;hh:mm&quot,A1-B1)

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[GlennUK]

The only way to work with negative times in Excel ( and to keep the times as actual Excel times ) is to switch on the 1904 Date System. To do this do menu command Tools/Options/Calculation and tick the 1904 Date System checkbox.

You can now work with negative times.

WARNING: This can really screw with your date fields in Excel, and I personally never use this any more, as the hassle of having dates being skewed by 4 years accidentally all over the place was more trouble than having access to negative dates.


Cheers, Glenn.

 

[xlbo]

LOL - looks like you have a faster connection than me Tony

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[TonyJollans]

Geoff - .. and I thought mine was slow

Glenn - Star for that piece of information

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.