 # 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

=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

• Re: Calculate Time From Whole Numbers

Super..Two thumbs up for excellence.. Just what I was looking for.

Thanks

John

• Re: Calculate Time From Whole Numbers

Biz, sorry to be so quick on the trigger.

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

• Re: Calculate Time From Whole Numbers

Quote from shg

Biz, sorry to be so quick on the trigger.

It is ok mate.

## Participate now!

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