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?
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?
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 JohnDrewIt 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
Re: Calculate Time From Whole Numbers
Quote from shgBiz, 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.
Don’t have an account yet? Register yourself now and be a part of our community!