Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
I tried a variation on this question earlier and recevied some useful
response but now that I understand my question more, maybe I acn narrow it down. I would like to store the time "15:00" in the cell but display "3". Not 3pm or 3:00pm, just "3". The "h" option in custom format gives me "15". -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200708/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
You have shown the number 3 in double quotes. Does that mean that you want it
to be text format or is that just the way you have described it? If you want it as text then the following nested formula will convert the hours of a time to text. It assumes that the time is in cell A1. =LEFT(TEXT(A2,"h am/pm"),FIND(CHAR(32),TEXT(A2,"h am/pm"))-1) However, if you want it to be a numeric value that you can perform mathematical operations on then use the following:- =VALUE(LEFT(TEXT(A1,"h am/pm"),FIND(CHAR(32),TEXT(A1,"h am/pm"))-1)) Regards, OssieMac "Emerogork via OfficeKB.com" wrote: I tried a variation on this question earlier and recevied some useful response but now that I understand my question more, maybe I acn narrow it down. I would like to store the time "15:00" in the cell but display "3". Not 3pm or 3:00pm, just "3". The "h" option in custom format gives me "15". -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200708/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
Format Cells Userdefined Format
in the Type box just type 3 "Emerogork via OfficeKB.com" skrev: I tried a variation on this question earlier and recevied some useful response but now that I understand my question more, maybe I acn narrow it down. I would like to store the time "15:00" in the cell but display "3". Not 3pm or 3:00pm, just "3". The "h" option in custom format gives me "15". -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200708/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
What do you mean by "Userdefined Format" in that context? I don't have
such an option uinder Format Cells in Excel 2003. If you type 3 into the Type box in Custom format, it will display 3 for 15:00, but it will also display 3 for 17:00, which probably isn't what the OP wants. -- David Biddulph "excelent" wrote in message ... Format Cells Userdefined Format in the Type box just type 3 "Emerogork via OfficeKB.com" skrev: I tried a variation on this question earlier and recevied some useful response but now that I understand my question more, maybe I acn narrow it down. I would like to store the time "15:00" in the cell but display "3". Not 3pm or 3:00pm, just "3". The "h" option in custom format gives me "15". -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200708/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
This formula will do the trick
=(A1*24)-(A1--"12:00")*12 "Emerogork via OfficeKB.com" wrote: I tried a variation on this question earlier and recevied some useful response but now that I understand my question more, maybe I acn narrow it down. I would like to store the time "15:00" in the cell but display "3". Not 3pm or 3:00pm, just "3". The "h" option in custom format gives me "15". -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200708/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
I don't think the OP's question can be solved the way he wants, you gave him
one option by using a second cell that converts the value to an integer and another would be am event macro that converts the value in the cell to an integer directly but to keep the actual value of 15:00 in the cell but displaying it as 3 is not possible -- Regards, Peo Sjoblom "Teethless mama" wrote in message ... This formula will do the trick =(A1*24)-(A1--"12:00")*12 "Emerogork via OfficeKB.com" wrote: I tried a variation on this question earlier and recevied some useful response but now that I understand my question more, maybe I acn narrow it down. I would like to store the time "15:00" in the cell but display "3". Not 3pm or 3:00pm, just "3". The "h" option in custom format gives me "15". -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200708/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format numbers/time format | Excel Discussion (Misc queries) | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
How to format cells in Excel for time in format mm:ss.00 | Excel Worksheet Functions | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |