Calculating and displaying negative time

[cheap_ash]

I need a formula to calcuate A2-B2 (both time/date fields) and if A2 is larger than B2, show the value as a negative. ie, if the result of A2-B2 is negative 21 hours, i need it to display - 0 21:00:00 (i'm using the d hh:mm:ss format on the output field)

I've tried concatenating "-" with an ABS calculation but I only get a serial answer back. I also tried using TIMEVALUE, but to no avail. I'm pretty stuck on this.

 

[PaddyD]

tools | options | calculation - selet 1904 date system

 

[cheap_ash]

one problem, what should be displayed as -0 4:09 is showing up as -1 4:09 (for 10/23/2006 22:00 - 10/23/2006 17:51) any ideas?

 

[Scott Huish]

I am using 1900 date system in this:

=IF(A2<B2,TEXT(B2-A2,"-hh:mm:ss"),A2-B2)

 

[cheap_ash]

this is the sheet, g2 is the first solution i was offered, g3 is the second... still running into a problem. :/

Book1
	A	B	C	D	E	F	G
1	Name	L.K.DATE/TIME	SpawnTimeDays	Downtime	N.S.DATE/TIME	ActualSpawn	Difference
2	MO	10/14/200619:00	9	3	10/23/200622:00	10/23/200617:51	-0104:09
3	3P	10/21/200612:45	7	2	10/28/200614:45	10/27/200610:45	-04:09:00
4	Matron	10/17/200622:00	9	3	10/27/20061:00	10/27/20061:00	0100:00
Sheet1

 

[Scott Huish]

Sorry, try this:

=IF(F2<E2,TEXT(E2-F2,"-d hh:mm:ss"),F2-E2)

Format cell as d hh:mm:ss

 

[cheap_ash]

is that outputting "-1 04:09:00" for you? b/c it's not -1 day, it's only -04:09:00 and it keeps telling me -1 day... thank you for trying everyone, btw, i don't mean to sound like an ingrate, just a very cranky/low sleep person right now :P

 

[Scott Huish]

No. it didn't.

My formula is in G3 (highlighted yellow):

Book1
	A	B	C	D	E	F	G
1	Name	L.K. DATE/TIME	Spawn Time Days	Downtime	N.S. DATE/TIME	Actual Spawn	Difference
2	MO	10/14/2006 19:00	9	3	10/23/2006 22:00	10/23/2006 17:51	-01 04:09
3	3P	10/21/2006 12:45	7	2	10/28/2006 14:45	10/27/2006 10:45	-0 04:09:00
4	Matron	10/17/2006 22:00	9	3	10/27/2006 1:00	10/27/2006 1:00	01 00:00
Sheet1

 

[cheap_ash]

thanks! appreciate it!