Forum Discussion

Musical1FL's avatar
Musical1FL
Copper Contributor
Feb 25, 2025

Formula for Subtracting End time from Start time to a Number

Hi ,I'm looking subtract an ending time (as in work shift) from starting time and rendering a number. 

I have the two time columns formulated as 'time' as 1:30 PM and want to make the hours worked a number. 

 

Thanks!

excel
Formulas and Functions

8 Replies

  • insightsgeek's avatar
    insightsgeek
    Brass Contributor
    Feb 26, 2025

    Hi,

    I tried the following formula:

    =(B2-A2)*24
    A2 => Start Time
    B2 => End TimeMultiplying by 24 converts the result from a time value to a decimal number representing hours.


    Example:

    Make sure the column with the result is formatted as a Number or General to display the correct output.

    Handling Overnight Shifts (Past Midnight)
    If shifts cross midnight, Excel may return a negative value. To fix this, use:

    =MOD(B2-A2,1)*24

    Hope this helps! Let me know if you need further clarification. 😊

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 25, 2025

    Let's say start time is in C2 and end time in D2.

    The number of hours is

    =24*MOD(D2-C2, 1)

    Format the cell with the formula as General or as Number with the desired number of decimal places.

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Feb 25, 2025

      Hans, why did you add the MOD?

      if it is possible they have days included with the time then I would think he would want that included

      e.g. (2025-01-02 18:00)  -  (2025-01-01 13:00)  should be 29 hours not 5 hours and if they are always the same date (or no date) then I don't think MOD should matter but maybe I'm missing something.

      also, if there is no date portion but overnight is possible (e.g. 11pm -> 2am) then try:

      =24*(D2 - C2 + (D2<C2) )

      • Musical1FL's avatar
        Musical1FL
        Copper Contributor
        Feb 25, 2025

        HI Guys,  thanks for your help..  I have another dilemma... I know what I want but not how to write the formula....  I've attached the spreadsheet....  the timesheet calculates hours for pay, but the first hour is $50 and subsequent hours are at $35/HR -- So I added some columns to do the calculations...  $50 in one column to be added as when the person enters hours.... then a formula in the next column taking total hours ..ex..  (J5 - 1)*35 to get he balance of the total amount due.. then add the $50 plus the next column -- It works fine, but when there are no hours populated yet.. and the 50 hasn't been entered, the total column is showing -$35 because the formula is calculating (0 - 1)*35   I'd like to show 0 in the total column so when I sum it to submit the hours, it's not subtracting all those -$35 amounts. 

         

        Thanks!

Resources