|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 19th, 2009, 07:41 AM
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
May 19th, 2009, 11:32 AM
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
May 20th, 2009, 07:49 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
