Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert to Hour
This is what I have done so far:
C4 is begin Time: 2/7/07 4:00PM D4 is endin Time: 2/8/07 1:30AM E4 is Time Differ: =(hour(d4-c4)*60+(d4-c4))."It shows the different tim. I35 is Total Time: Then I use Sum to count all the time. It shows 40:30 Which is 40 hours and 30 minutes. How I can convert it to 40.5 hour or other way I can use it to do some other calculation. Please help! Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert to Hour
Multiply 40:30 by 24 and format the cell as a number.
(Excel stores time as fractions of a 24 hour day; multiplying by 24 cancels the 24 and formatting as a number, as opposed to time, converts 40:30 to the decimal form 40.5.) Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Jimmy" wrote: This is what I have done so far: C4 is begin Time: 2/7/07 4:00PM D4 is endin Time: 2/8/07 1:30AM E4 is Time Differ: =(hour(d4-c4)*60+(d4-c4))."It shows the different tim. I35 is Total Time: Then I use Sum to count all the time. It shows 40:30 Which is 40 hours and 30 minutes. How I can convert it to 40.5 hour or other way I can use it to do some other calculation. Please help! Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert to Hour
Can you give me an example what it looks like? =int((i35)*24) It does not
look right! Please tell me what it should be! Thanks! "Dave F" wrote: Multiply 40:30 by 24 and format the cell as a number. (Excel stores time as fractions of a 24 hour day; multiplying by 24 cancels the 24 and formatting as a number, as opposed to time, converts 40:30 to the decimal form 40.5.) Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Jimmy" wrote: This is what I have done so far: C4 is begin Time: 2/7/07 4:00PM D4 is endin Time: 2/8/07 1:30AM E4 is Time Differ: =(hour(d4-c4)*60+(d4-c4))."It shows the different tim. I35 is Total Time: Then I use Sum to count all the time. It shows 40:30 Which is 40 hours and 30 minutes. How I can convert it to 40.5 hour or other way I can use it to do some other calculation. Please help! Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert to Hour
I don't know what you're trying to do with your formula
=(hour(d4-c4)*60+(d4-c4)) The (d4-c4) term is in Excel time units (number of days), so by adding your extra term of hour(d4-c4)*60 you are adding 540 days to the time difference! If you want the time difference in Excel time units, just use =(d4-c4) and format as time. If you want it in hours, use =(d4-c4)*24 and format as number or general. You can either add the times (and format as [h]:mm to avoid wrapping round beyond 24 hours), and then multiply by 24 to convert to hours and format as number or general, or convert to hours when you do the subtraction and add the results. -- David Biddulph "Jimmy" wrote in message ... This is what I have done so far: C4 is begin Time: 2/7/07 4:00PM D4 is endin Time: 2/8/07 1:30AM E4 is Time Differ: =(hour(d4-c4)*60+(d4-c4))."It shows the different tim. I35 is Total Time: Then I use Sum to count all the time. It shows 40:30 Which is 40 hours and 30 minutes. How I can convert it to 40.5 hour or other way I can use it to do some other calculation. Please help! Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert to Hour
If 40:30 is in A1, then =A1*24. Format as a number, not time.
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Jimmy" wrote: Can you give me an example what it looks like? =int((i35)*24) It does not look right! Please tell me what it should be! Thanks! "Dave F" wrote: Multiply 40:30 by 24 and format the cell as a number. (Excel stores time as fractions of a 24 hour day; multiplying by 24 cancels the 24 and formatting as a number, as opposed to time, converts 40:30 to the decimal form 40.5.) Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Jimmy" wrote: This is what I have done so far: C4 is begin Time: 2/7/07 4:00PM D4 is endin Time: 2/8/07 1:30AM E4 is Time Differ: =(hour(d4-c4)*60+(d4-c4))."It shows the different tim. I35 is Total Time: Then I use Sum to count all the time. It shows 40:30 Which is 40 hours and 30 minutes. How I can convert it to 40.5 hour or other way I can use it to do some other calculation. Please help! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert time to the nearest 1/4 hour with a 7 min. window | Excel Worksheet Functions | |||
convert decimal numbers to a fraction of an hour for payroll hour | Excel Worksheet Functions | |||
Convert Date/Time to the Hour | Excel Discussion (Misc queries) | |||
convert time from 60 minute hour to 100 minute hour | Excel Discussion (Misc queries) | |||
Convert decimal hour into time format? | Excel Discussion (Misc queries) |