Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date and time format
Hi, I'd appreciate a solution if any to my query.
I have entered a date and time in a cell as (9mar09 24:00) The cell is formatted to show date and time and it correctly reads it as 10th Mar 09 00:00. However I want the cell to show me 9th Mar 09 24:00, which is really the same time. It is not happening, is this possible to do, appreciate your help. Sorab |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date and time format
=IF(MOD(A1,1)=0,TEXT(A1-1,"dd mmm yy")&" 24:00",A1) but of course you
couldn't use the result in calculation as it is a text string, not a number. As a matter of interest, how did you format the cell to show "10th Mar"? Any answers which I have seen to the "st, nd, rd, th" question have involved converting to a text string, rather than formatting. -- David Biddulph "sorab" wrote in message ... Hi, I'd appreciate a solution if any to my query. I have entered a date and time in a cell as (9mar09 24:00) The cell is formatted to show date and time and it correctly reads it as 10th Mar 09 00:00. However I want the cell to show me 9th Mar 09 24:00, which is really the same time. It is not happening, is this possible to do, appreciate your help. Sorab |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date and time format
On May 19, 2:32*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: =IF(MOD(A1,1)=0,TEXT(A1-1,"dd mmm yy")&" 24:00",A1) but of course you couldn't use the result in calculation as it is a text string, not a number. As a matter of interest, how did you format the cell to show "10th Mar"? Any answers which I have seen to the "st, nd, rd, th" question have involved converting to a text string, rather than formatting. -- David Biddulph "sorab" wrote in message ... Hi, I'd appreciate a solution if any to my query. I have entered a date and time in a cell as (9mar09 24:00) The cell is formatted to show date and time and it correctly reads it as 10th Mar 09 *00:00. However I want the cell to show me 9th Mar 09 24:00, which is really the same time. It is not happening, is this possible to do, appreciate your help. Sorab- Hide quoted text - - Show quoted text - Thanks David. You are right it works as a text string, hence cannot use for calculations. I apologize. I did not format the cell as " 10th Mar...", but as "10 Mar 09 00:00" No "th"... did'nt realize that until you spotted it. I think Excel does not allow a date and time to display as 24:00... Sorab |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time and Date Format | Excel Discussion (Misc queries) | |||
Date Format (No Time) | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
how do I format cells to change date and time to just date | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |