Forum Discussion

Tiffa1988's avatar
Tiffa1988
Copper Contributor
Oct 14, 2023

How do I add and subtract decimals in separate columns?

I am trying to make comp time balances automatically populate just by putting in how much time someone is earning or how much time someone is using. The time is minutes to decimals. Anyone know how I can accomplish this?

excel

2 Replies

Sort By
  • rachel's avatar
    rachel
    Iron Contributor
    Oct 15, 2023

    Tiffa1988 

     

    I think you can use =TIME() function.

    e.g. to calculate time interval between 14:15 to 14:30, you can input 

    =TIME(14, 30, 0)-TIME(14, 15, 0)

     

    If you want to format the output, you can use =TEXT() function:

    =TEXT(TIME(14, 30, 0)-TIME(14, 15, 0),"hh:mm:ss;@")

     

    output will be like this:

     

     

     

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Oct 15, 2023
    So if you enter a 'Time' value like 0:15 for 15 min the actual decimal value is in fraction of a day. so if you add 6:30 + 5:30 it will be 12:00 or 0.5
    So if you are adding or subtracting time values but the result is formatted as decimal or number (or general and excel is defaulting to number) you will see a very small number. You can change the cell format to show h:mm.0 (go to format -> custom and type that into the format bar) or you can *24*60 the values so the decimal value is in minutes (note it will not be a time value it will be a decimal value)

Resources