• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Negative Time Error

M

malbarki

Member
Hi guys

I'm trying to calculate the delay between two duration, I used a straightforward formula A1-B1 and it's working fine till the result comes in a negative form it came as endless ########

Ex.1 (If result positive)
Target Response Time (A1) = 27:00
Actual Response Duration (B1) = 5:24
Expected Result (C1) = 21:36

Ex.2 (If result negative)
Target Response Time (A1) = 27:00
Actual Response Duration (B1) = 30:24
Expected Result (C1) = -02:36
 
Maybe this will solve your issue...
Go in the file menu, option, advanced, section "when calculating with this workbook "
and select USE "Use 1904 date system"
 
GFC said:
Maybe this will solve your issue...
Go in the file menu, option, advanced, section "when calculating with this workbook "
and select USE "Use 1904 date system"
Click to expand...

Will this affect any other calculation I have in the same workbook?
 
Hi,

Malbarki,

Just remove the - sign from your text function and check the 1904 date system in option. I think you will get your result.

Regards!
 
Hii malbarki,

Please try this in cell C2:

Code: 
=IF(HOUR(A2)>HOUR(B2),0,"-"&TEXT(ABS(B2-A2),"HH:MM"))

Hope that helps..
 
Hi malbarki,

Going on your first example
Ex.1 (If result positive)
Target Response Time (A1) = 27:00
Actual Response Duration (B1) = 5:24
Expected Result (C1) = 21:36

Ex.2 (If result negative)
Target Response Time (A1) = 27:00
Actual Response Duration (B1) = 30:24
Expected Result (C1) = -02:36
Click to expand...

Try this
=IF(A1>=B1,A1-B1,"-"&TEXT((B1-A1),"hh:mm")) and copy down, this will give any negative time values as TEXT all the others will be NUMERIC

BTW - I get -3:24 in Ex.2, don't know how you arrive at -2:36?
 
oldchippy said:
Hi malbarki,

Going on your first example


Try this
=IF(A1>=B1,A1-B1,"-"&TEXT((B1-A1),"hh:mm")) and copy down, this will give any negative time values as TEXT all the others will be NUMERIC

BTW - I get -3:24 in Ex.2, don't know how you arrive at -2:36?
Click to expand...

There's some issues with some of the entries..See attched file

As for Ex.2 you're right it's look like i'm starting to lose my focus with all these numbers o_O :oops:
 

Attachments

  • Using the TEXT Function.xlsx
    12.7 KB · Views: 2
Hi,

In your spreadsheet all your values are TEXT, so does this work for you?

=VALUE(A2-B2) copy down and format the cells to [h]:mm
 
You must log in or register to reply here.
Back
Top