Getting rid of the time/date serial number

[Lorlai]

For some reason, when I compare two dates, excel is only reading the serial number. How can I get just the date/time stamp, not the serial number?

For example, I want 08/10/2011 12:00:00 PM, not 389985...


Thank you!

 

[wigi]

If you have this in cell A1:

=A1-INT(A1)

You subtract the integer part of the date, so that only the decimals remain. These are used to indicate fractions of a day (=1)

 

[Lorlai]

My formula is comparing cells. So in DZ1, the formula is =A1=A20. Would this be affecting the serials?

 

[wigi]

Do you need to remove the days from both A1 and A20?

 

[Lorlai]

I'm not removing anything, just comparing. Full formula is

=IF(CE748=CE12747,CE748,"!" & CE748)

I want to keep the full date/time, and only add a ! if they do not equal

 

[wigi]

For example

=IF(INT(CE748)=CE12747,"","!") & CE748

This is a gues, you did not provide any more details about your cells and their values.

 

[Lorlai]

Thanks for the formula. It didn't work in this situation.

I'm not sure what further details I can provide....

I have a large amount of data, and some of this data includes dates, and date/times. For instance, on cell t1 the date/time is 11/30/2010 12:00:00 AM, and is the same on cell t12000. In theory, the formula should give 11/30/2010 12:00:00 AM. Instead, the formula yields 40512.

What else can I provide to maybe help this?

Thank you!

 

[wigi]

What else can I provide to maybe help this?

- What is the real cell contents of cells CE748 and CE12747? (Look at the cell contents, not how it is formatted in the cell).

- What should be the result of the formula?

 

[Lorlai]

The Real Cell Content of CE748 is 11/30/2010 0:00. The real cell content of CE12747 is 05/30/2011 0:00. The Result should look like !11/30/2010 0:00 (the same as CE748 with an ! added to the beginning).

Is this more clear?

 

[wigi]

Maybe this attempt?

=IF(CE748-INT(CE748)=CE12747-INT(CE12747),"","!") & TEXT(CE748,"mm/dd/yyyy h:mm")