time conversions

  • Thread starter Thread starter Soph
  • Start date Start date
S

Soph

Hi
I am not sure what is going on here but I have timesteps
of 15min from midnight to 11.45pm in the following format:
0000 to 2345 which I am tring to convert to 00:00 to 23:45
format. I have been using the below formula but it only
works for certain hours -
=TIME(INT(A1/100),MOD(A1/100,2)*100,0)
for eg. from 0000-0045 the formula converts this correctly
to 12:00 AM - 12:45 AM. But from 0100-0145 it converts to
2:40 AM - 3:25 AM.
Why is this happening? and can I avoid it?
Cheers
Soph
 
Not sure why it's not working,
but maybe try instead ...

If numbers are in col A, A1 down
(between 0 to 2359)

Put in B1:
=(LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))+0
Copy down

Format col B as Time ("13:30" format)
 
Soph,

Try this

=TIME(INT(A1/60),A1-INT((A1/60))*60,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top