Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time as 7.5 (which represents 7:30 AM) to 0730
I have downloaded data that has 7.5 in cell G2. I used G2*100 to get 0750 in
cell I2, formatted as 0000. To convert the .5 hour (50) to 30 as minutes, I used in J2 (right, I2,2) to pull out the 50, and then in K2 =IF(J2="50","30",J2). This does give me the 30 I need (or the 00 for exact hour times), and then in L2 I have =LEFT(I2,2)&K2 which gives me the correct result in cases where the number is or = 10. Single digits, such as 7 produce 7530 instead of 0730. It seems like it's ignoring the zero in front of the 7 in I2, and therefore using the 2 left non-zero #'s, the 75, and the result is 7530. G2 I2 J2 K2 L2 7.5 0750 50 30 7530 <- wrong should be 0730 (rep.7:30 AM) 21.5 2150 50 30 2130 <- correct (representing 9:30 PM) Is there another simplier way, or a way to fix this problem. Basically I need to convert #'s such as 7.5 to 0730. Thanks, Steve |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Taking out string part which represents date | Excel Worksheet Functions | |||
Converting time formats into actual time(minutes) | Excel Discussion (Misc queries) | |||
Converting Eastern time to Central time | Excel Discussion (Misc queries) | |||
Converting decimal time to standard time? | Excel Discussion (Misc queries) | |||
Can't stop time duration [hh]:mm:ss converting to time of day | Excel Discussion (Misc queries) |