Worksheet Dates - different

[AlexanderBB]

My workbook has several worksheets, each with some Date columns displaying format dd mmm yyyy.
However if I click in the date cell, the formula bar shows dd mm yyyy - in all but one of the worksheets.
Yet looking at the cell format all are set the same - to Custom dd\ mmm\ yyyy. (That also puzzles me, is "\ " something special?)

Why doesn't the formula Bar show the same for all Dates/Sheets?

 

[pgc01]

Hi

I'd say that some of them are not dates.

Easy to check, if A1 holds one of those dates, just use

=ISNUMBER(A1)

to check if the value in A1 is a date.

(That also puzzles me, is "\ " something special?)

The \ is just an escape character. In this case it's not necessary.

 

[AlexanderBB]

Hi, thanks for that info. This will sound crazy but I don't know where/how to apply =ISNUMBER(A1)
I've only used VBA code modules.. and I think this is the mysterious worksheet function ?

If they aren't real dates, is it easy to change them to real dates? Better to have consistency?
Thanks for clarifying escape char.

 

[Dryver14]

just type =ISNUMBER(A1) on any empty cell in the worksheet it will return true or false

 

[AlexanderBB]

Ah... thanks. It returns True where the formula bar shows dd mm yyyy and False where it's dd mmm yyyy. So It means my dates are numbers?
How would I best change them to dates? (There's several hundred to do).A

On looking further a Right click - Format Cells shows ALL are category Custom. Thus why or how could they be different ?

 

[Dryver14]

One last thing to try here

go to file/options/advanced/display options for this worksheet and see if show formulas in cells instead of their calculated results is checked

 

[AlexanderBB]

Thanks for the suggestion, but no it's not ticked (for every sheet).

There's no way to convert a number Date to a date Date ? I tried reading the value, clearing the cell, format it as Custom dd mmm yyyy and write the value back.
But it made no difference. It displays in the cell correctly but still shows as dd mm yyyy in the Formula Bar

So I repeated that in the worksheet that is OK, and it also did the same thing. Which of course, made a good cell bad.

I tried the format painter but no luck there either. So formatted it back to what it was and entered the value again, but it stays wrong.

Does anyone know what's happening here ?

 

[pgc01]

There's no way to convert a number Date to a date Date ?

There is no Date type in the excel worksheet. A date in the worksheet is just a number, that you interpret as a date.

The values that you see as dd mmm yyyy and that give you true with the isnumber() are the ones that are dates. The ones that result in false with the isnumber() are text.

The date you see in the formula bar is in the default format for dates in your pc.

Remark:
A similar thing happens with a number. If you have the number 1.234 and the cell formatted as 0.0 you'll see in the cell 1.2 but in the formula bar you still see 1.234

The format in the cell does not change a value, simply controls the way it is displayed.

The formula bar displays the value in the default format, independently from the custom format you set in the cell.

 

[AlexanderBB]

> The date you see in the formula bar is in the default format for dates in your pc.

Thanks PGC, I get that now. Maybe it doesn't matter what the formula bar shows, but it bugs me that its not the same for everything.
That is, why are some entries are seen as dates, and some text - when Format Cells—Category shows the same for both (neither Date or Text, but Custom)?
What actually determines the cell format and how do you change one Category to another?

If I change Category from Custom to Text dates becomes a number, whereas Text remains as it looks. Something is wrong here... I'm instructing the cell to be text and yet it becomes a number.
Meaning the underlying data type (text or date) is known to the category dialog. Whereabouts is this datatype held?

LOL sorry for so many questions..