Calculate Time From Whole Numbers

  • It is quicker and easier for me to enter times as whole numbers.


    I tried to Catinate the number and enter the ":" in the middle. Example
    I enter 815 and then =":" &RIGHT(a1,2) and get :15 but how do I add the 8 and convert the new number to time?

  • Re: Calculate Time From Whole Numbers


    = LEFT(A2,LEN(A2)-2) & ":" & RIGHT(A2,2)


    But I'd like to add that this is bad in every way I can think of. Marginally better is


    = INT(A1/100)/24 + MOD(A1,100)/1440


    and format as hh:mm. That way you store it in Excel date format.


    How hard is it to type a colon?

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Calculate Time From Whole Numbers


    How about this?


    =IF(LEN(A1)=3,TIME((LEFT(A1,1)),MID(A1,2,2),0),TIME((LEFT(A1,2)),MID(A1,3,2),0))


    The above formula should work if your whole number is either 3 or 4 characters long.


    :ninja:

  • Re: Calculate Time From Whole Numbers


    Quote from JohnDrew

    It is quicker and easier for me to enter times as whole numbers.


    I tried to Catinate the number and enter the ":" in the middle. Example
    I enter 815 and then =":" &RIGHT(a1,2) and get :15 but how do I add the 8 and convert the new number to time?


    Try formula below


    =VALUE(LEFT(TEXT(A1,"0000"),2)&":" &RIGHT(TEXT(A1,"0000"),2))


    Format
    Format->Cells->Custom Format
    hh:mm


    Biz

  • Re: Calculate Time From Whole Numbers


    Alternatively, borrowing a part of shg's solution, this could do it:


    =TIME((LEFT(A1,LEN(A1)-2)),RIGHT(A1,2),0)


    I am pretty sure that this way does not require tweaking the format of your cell...


    :ninja:

  • Re: Calculate Time From Whole Numbers


    Quote

    =LEFT(TEXT(A1,"0000"),2) & ":" & RIGHT(TEXT(A1,"0000"),2)
    Format->Cells->Custom Format
    hh:mm


    Biz, the result of that is a string -- formatting won't convert it to a date. You could do


    =VALUE(LEFT(TEXT(A1,"0000"),2) & ":" & RIGHT(TEXT(A1,"0000"),2))


    and format it as hh:mm

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Calculate Time From Whole Numbers


    Quote from shg

    Biz, the result of that is a string -- formatting won't convert it to a date. You could do


    =VALUE(LEFT(TEXT(A1,"0000"),2) & ":" & RIGHT(TEXT(A1,"0000"),2))


    and format it as hh:mm


    Shg,


    You are right, but I changed my result before you posted your suggestion.
    Thanks for pointing out my mistake.


    Biz

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!