Solution for displaying negative time values (almost - help!)

Question

I recently found a situation where I needed to observe negative time values. All I found online was "It isn't possible, change to 1904 date system" and so on.

I thought about it, and I think I've nearly found a solution for finding the difference in hours and minutes between two time values, both positive and negative. Here it is:

=IF(A2-B2>0, IF(ROUNDDOWN(ROUND((B2-A2)*24,2),0)<-1, ROUNDDOWN(ROUND((B2-A2)*24,2),0), "-"&ROUNDDOWN(ROUND((B2-A2)*24,2),0))&":"&ROUND(IF(ROUND((ROUNDDOWN(ROUND((B2-A2)*24,2),0)-ROUND((B2-A2)*24,2))*60,2)<10, 0&ROUND((ROUNDDOWN(ROUND((B2-A2)*24,2),0)-ROUND((B2-A2)*24,2))*60,2), ROUND((ROUNDDOWN(ROUND((B2-A2)*24,2),0)-ROUND((B2-A2)*24,2))*60,2)),0), ROUNDDOWN(ROUND((B2-A2)*24,2),0)&":"&ROUND(IF(ROUND((ROUND((B2-A2)*24,2)-ROUNDDOWN(ROUND((B2-A2)*24,2),0))*60,2)<10, 0&ROUND((ROUND((B2-A2)*24,2)-ROUNDDOWN(ROUND((B2-A2)*24,2),0))*60,2), ROUND((ROUND((B2-A2)*24,2)-ROUNDDOWN(ROUND((B2-A2)*24,2),0))*60,2)),0))

Where A2 is time A and B2 is time B.

Here are the results I got:

TIME A           TIME B               Difference

7:00:00  PM   7:45:00  PM       0:45

10:00:00 PM  9:21:00  PM      -0:39

7:21:00  AM   6:37:00  AM      -0:44

7:21:00  AM   6:37:00  AM      -0:44

5:45:00  AM   5:00:00  AM      -0:45

6:37:00  AM   5:45:00  AM      -0:52

5:00:00  PM   4:01:00  PM      -0:59

2:42:00  PM   2:21:00  AM      -12:21

6:55:00  PM   3:45:00  AM      -15:10

3:59:00  AM   12:31:00 AM     -3:28

5:00:00  PM   7:00:00  PM       2:0

As you can see, the formats come out quite nicely and with due caution, they can be sorted from largest to smallest. The only issues I can find are as follows:

1. When there are 0 minutes, it displays a single zero when I want it to display double zeros
2. When the difference is between -10 and +10 hours, the hour needs to be displayed with a zero before it in order to be sorted properly.

I'm so deep into the function that I can't figure out quite where to insert the IF(-10<(B2-A2)<0, "-00:"&...) and IF(0<(B2-A2)<10,"00:"&...).

This is what happens when the values are sorter in their corrent form:

0:45
2:0
-0:39
-0:44
-0:44
-0:45
-0:52
-0:59
-12:21
-15:10
-3:28

See how -3:28 is at the end, instead of before -12:21? This is all fixed when -3.28 becomes -03.28. In addition for the purpose of not looking tacky, 2:0 would have to become 02:00 as well.

Any help would be greatly appreciated!

Answer 1

I'd use a much simpler formula:

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

Answer 2

sorry for that sir

actually  i saw your reply to the answer was excellent

for that reason i wanted you to see my question

try for solution

sorry and thankyou

Answer 3

You seem to have posted this as a reply to an unrelated thread.

I would suggest you re-post as a new thread.

That way people who are interested in answering your question will see it, and people who subsequently search for a similar answer are more likely to find it.

Moreover you will give us information about which version of Excel and Windows you are using, which is often helpful.

Answer 4

VBA FOR DOWNLOADING STOCK PRICE FROM www.nseindia.com

sir there is excel file i am uploaded in drop box file with the link below

https://www.dropbox.com/s/rkj0409tdadu8hp/new.xlsm

in this excel the data is downloading from yahoo finance i want it to change to www.nseindia.com for more accuracy 

in this even it is only for stock prices but i am looking for stock future prices with 90 days or 3 months data 

for more details i have explained in excel file

in the first side of excel it is original excel who has uploaded in website

in the second side i have explained what i need with pitcher 

thank you in ADVANCE

genenily i am very much need of this to save lot of time 

this is used for further calculation 

if any clarification or dought can mail me at

******@hotmail.com

THANK YOU ONCE AGAIN

Answer 5

How about:

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