date and time format

[sorab]

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

 

[David Biddulph]

=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]

=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.

 

[sorab]

=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






- 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

 

[sorab]

=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






- 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