Time conversion - Again!!

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Apr 25, 2006

• #2

Try

=RIGHT(0&TEXT(A1/24,"[hh]mm"),5)

note:edited to account for hours over 100, although I'm not sure what you'd require if hours go over 1000.....

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Apr 25, 2006

• #3

...actually a better way...

=TEXT(A1/24,"[hhh]mm")

although this will always round the minutes down, so 7.33 will give you 00719 when to the nearest minute it should be 00720. If you're worried about that use

=TEXT(ROUND(A1*6,1)/144,"[hhh]mm")

 

Upvote 0

S

starryeyed

Board Regular

Joined

Jun 22, 2005

Messages

221

• Apr 25, 2006

• #4

Did you see my answer here?
http://www.mrexcel.com/board2/viewtopic.php?t=208541&highlight=

 

Upvote 0

ByteMe

Board Regular

Joined

Mar 9, 2005

Messages

85

• Apr 25, 2006

• #5

Date conversion

Barry - that was perfect, exactly what i needed. Starryeyed - it does work but if the hours are 108 then it produces the answer of 0010800 and i need the answer to have a maximum of 5 digits.

Thanks to you both.

 

Upvote 0

ByteMe

Board Regular

Joined

Mar 9, 2005

Messages

85

• Apr 25, 2006

• #6

Just a quick thought....

Why do you multiply by 6 & then divide by 144??

e.g. =TEXT(ROUND(A1*6,1)/144,"[hhh]mm")

Regards

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Apr 25, 2006

• #7

To round A1/24 to the nearest minute (1/1440th of a day)

=ROUND(A1*1440/24,0)/1440

this is the same as

=ROUND(A1*60,0)/1440

which you can simplify to

=ROUND(A1*6,1)/144

If you have Analysis ToolPak enabled you could use MROUND and

=TEXT(MROUND(A1/24,1/1440),"[hhh]mm")

 

Upvote 0

ByteMe

Board Regular

Joined

Mar 9, 2005

Messages

85

• Apr 26, 2006

• #8

Appreciate the response - thank you very much

 

Upvote 0