Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Huge problem with time
hello. I have a problem here which I don't know that can be solved.
I need to transform some decimals to time and in another column know the time in days. I have this e columns: 34 0,875 34,875 if I divide by 24 and format as time I have this: 10:00 0:52 10:52 The problem is when the sum is bigger then 24 hours, so I have this: 0,13 0,91 1,04 And is show me this: 3:00 21:52 0:52 and I need this: 3:00 21:52 24:52 But if I format all column with this format: [h]:mm:ss;@ In the first example I have get this: 10:00 0:52 34:52:30 How can I fix this? Thanks, Marco |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Huge problem with time
Assuming these are decimal hours thus if you format as [h]:mm:ss
and divide them with 24 you should get 34:00:00 0:52:30 34:52:30 and that is what I get if I do the same so apparantly you haven't formatted the cell that returns 10:00 -- Regards, Peo Sjoblom "Marco" wrote in message ... hello. I have a problem here which I don't know that can be solved. I need to transform some decimals to time and in another column know the time in days. I have this e columns: 34 0,875 34,875 if I divide by 24 and format as time I have this: 10:00 0:52 10:52 The problem is when the sum is bigger then 24 hours, so I have this: 0,13 0,91 1,04 And is show me this: 3:00 21:52 0:52 and I need this: 3:00 21:52 24:52 But if I format all column with this format: [h]:mm:ss;@ In the first example I have get this: 10:00 0:52 34:52:30 How can I fix this? Thanks, Marco |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Huge problem with time
I not sure if your time is in hours or days. But the code below should get
your answer. I find the only way to get hours when they are greater than 24 is to use the last line of the code below (or similar). You have to count the number of days and multiply by 24 then add to the number of hours. You need to use the format statement to make sure you have a two digit number of minutes. Nobody like to see 6:2 when we are use to 6:02. TimeDays = 34.875 WholeDays = Int(TimeDays) FractDays = TimeDays - WholeDays Hours = Hour(FractDays) + (24 * WholeDays) TextTime = Hours & ":" & Format(Minute(FractDays), "#00") "Marco" wrote: hello. I have a problem here which I don't know that can be solved. I need to transform some decimals to time and in another column know the time in days. I have this e columns: 34 0,875 34,875 if I divide by 24 and format as time I have this: 10:00 0:52 10:52 The problem is when the sum is bigger then 24 hours, so I have this: 0,13 0,91 1,04 And is show me this: 3:00 21:52 0:52 and I need this: 3:00 21:52 24:52 But if I format all column with this format: [h]:mm:ss;@ In the first example I have get this: 10:00 0:52 34:52:30 How can I fix this? Thanks, Marco |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New Add-in in Excel 2003 - Huge Problem | Excel Discussion (Misc queries) | |||
huge problem | Excel Discussion (Misc queries) | |||
huge problem with 1 excel doc | Excel Discussion (Misc queries) | |||
Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
import problem on huge xls file | Excel Worksheet Functions |