| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing formating
I have h:mm format in one cell, I would like to change the format to next
cell as number but keep the number same for expample. a b 0:21 required to have 21 Please help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing formating
Time is stored as a fraction of a day
B1: ="required to have " & A1*24 "Kimti" wrote in message ... I have h:mm format in one cell, I would like to change the format to next cell as number but keep the number same for expample. a b 0:21 required to have 21 Please help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing formating
"Kimti" wrote:
I have h:mm format in one cell, I would like to change the format to next cell as number but keep the number It is unclear what you want. 1. If B1 should be total time in minutes, stored as time (fraction of a day): B1: =A1 with Custom format "[m]" without quotes. 2. If B1 should be just the minutes, stored as time: B1: =MOD(TRUNC(A1*1440),60)/1440 with Custom format "[m]" without quotes. 3. If B1 should be total time in minutes, stored as integer: B1: =TRUNC(A1*1440) with General format. 4. If B1 should be just the minutes, stored as integer: B1: =MOD(TRUNC(A1*1440),60) with General format 5. If B1 should have the string "required to have" followed by total time in minutes: B1: ="required to have " & TRUNC(A1*1440) 6. If B1 should have the string "required to have" followed by just the minutes: B1: ="required to have " & MOD(TRUNC(A1*1440),60) ----- original message ----- "Kimti" wrote in message ... I have h:mm format in one cell, I would like to change the format to next cell as number but keep the number same for expample. a b 0:21 required to have 21 Please help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing formating
Assume that 0:21 is in cell A2 Put the following formula in cell B2 =A2*24*60 The explanation is that time is a fraction of one day. Therefore multiply the fraction by 24 to change it to hours then multiply by 60 to change it to minutes. -- Regards, OssieMac |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing formating
Forgot to say that you need to set the number format in cell B2 to Number with no decimal places. -- Regards, OssieMac "OssieMac" wrote: Assume that 0:21 is in cell A2 Put the following formula in cell B2 =A2*24*60 The explanation is that time is a fraction of one day. Therefore multiply the fraction by 24 to change it to hours then multiply by 60 to change it to minutes. -- Regards, OssieMac |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing formating
.... or to General.
-- David Biddulph "OssieMac" wrote in message ... Forgot to say that you need to set the number format in cell B2 to Number with no decimal places. -- Regards, OssieMac "OssieMac" wrote: Assume that 0:21 is in cell A2 Put the following formula in cell B2 =A2*24*60 The explanation is that time is a fraction of one day. Therefore multiply the fraction by 24 to change it to hours then multiply by 60 to change it to minutes. -- Regards, OssieMac |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| changing default formating when I email an excel file as an attac. | Setting up and Configuration of Excel | |||
| formating movement without changing text | Excel Worksheet Functions | |||
| copying a cell and changing the formating from number to text | Excel Discussion (Misc queries) | |||
| Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
| Expanding conditional formating with reference cells changing | Excel Discussion (Misc queries) | |||
Linear Mode
