Forum Discussion

johnsboxftm's avatar
johnsboxftm
Brass Contributor
May 22, 2023

Time entering and calculated totals

Hello, everyone!

 

I would like to understand how to enter times and then have excel calculate the hours.  I would like to know if it is possible to NOT have to use the colon when doing so.  We go by 15 minute increments at my job so it would be ideal if I could type 9a into the cell and excel read and display it as 9:00, 1015a for 10:15 am, 5p for 5pm, 1130p for 11:30 pm and so on.  Then another cell that displays the total hours for that row.  I do know basic formulas such as =SUM, =A1-B1, =concat, etc.

excel
Formulas and Functions

1 Reply

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 22, 2023

    if you have excel 365 you can add this as a Lambda function (either using Excel Labs or directly in the Name Manager):

     

    ttime=lambda(in, if(AND(isnumber(in),IFERROR(in<1,0)),in, let(
         inTemp, SUBSTITUTE(in,"m",""),
         intime, IFERROR(--inTemp,--left(inTemp,len(inTemp)-1)),
             pm,0.5*(upper(right(inTemp,1))="P") ,
            out, if(len(intime)>2,(left(intime,len(intime)-2)+ right(intime,2)/60)/24,intime/24)+pm,
         out)));

     


    then by calling =ttime(A1) it will convert what it sees in A1 to time based on:
    a) it is already excel recognized time input
    b) it is 1-4 digits followed by "a", "p", "am", or "pm" (actually it only cares that there is only 1 non-"m" character and if that 1 non-"m" character is a "p" it is afternoon)
    c) if it is only a number (e.g. 1015 or 2100) it will assume it is also a time
    any other format or combinations of formats will probably result in errors or bad time values. Hope it is helpful