Forum Discussion

Kay_T1060's avatar
Kay_T1060
Copper Contributor
May 20, 2020

Time Differences Negative and Positive

Hi, 

 

I am wondering if there is a way to change negative numbers in Excel to positive numbers, but also keep the already positive numbers that are there. 

 

I am calculating the time between an appointment. 

 

Here is the current formula I am using: =IF(M3=M2,(K3-L2)*96,"") M=Place K= Start Time L=End Time, my issue is that it converts some of the numbers negative if the line above it is from a different date, and if time overlaps some how. 

 

I understand I can do the *-1, but that will turn the negative numbers to positive and then turn my positive numbers to negative

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 21, 2020

    Kay_T1060 

    Not sure what you'd like to calculate, but if answer literally

    =IF(M3=M2,SIGN(K3-L2)*(K3-L2)*96,"")

    ABS on entire formula returns an error in case of empty string.

  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    May 20, 2020

    Kay_T1060 , Hi,  function ABS changes negative no. to positive but keeps positive nos. positive. So say, =ABS(-3) = 3  and =ABS(3) = 3 as well

     

    In your case, it would bedone like  =ABS(IF(M3=M2,(K3-L2)*96,""))

     

    Hopefully that solves your issue (and if i understood your problem statement correctly)

     

  • peteryac60's avatar
    peteryac60
    Iron Contributor
    May 20, 2020

    Kay_T1060 

     

    Not sure what you mean that the line above is a different date. Can you put in a check that current line and previous line have the same date before you do your calculation?

     

    Generally , I would recommend using the Excel time functions (e.g. timevalue) for calculating time differences.

     

    Not sure if that helps you?

     

    Peter

Resources