Did User type "01/01/08" or "39448"?

[mikemundy]

If a cell contains the number 39448 is there any way for a macro to determine if the user typed "39448" or the user typed "01/01/08"?

In other words, I want the macro to be able to identify that a date with separators was entered rather than a number.

Thanks,
Mike

[shg]

Welcome to the forum.

Only if the cell were formatted as text, in which case, the number would show up as the number and not a date.

[royUK]

The date that you see is only a number formatted as a date. To see what i mean type today's date in any cell, then format it as a number, not a date - you will get 39657 which is 28 July 2008

[mikemundy]

Thanks, but I guess I didn't phrase my question well.

I understand that dates are stored as numbers.
I understand formatting and my question does not concern formatting.

In a macro, if cell.value = 39448,
is there any way for the macro to tell if the data was
entered as "01/01/08" rather than "39448"?

(I want the macro to adjust the number if it was entered as a date but to
leave it unchanged if it was entered as a number.)

I think the answer is "no" but I welcome other opinions or suggestions.

Mike

[royUK]

try

Please Login or Register  to view this content.

[mikemundy]

try

Please Login or Register  to view this content.

But, because the date is stored as a number, I don't believe that the above expression EVER returns "TRUE".

[shg]

You should try it.

But even though it does when the cell is formatted as a date (with slashes), it tells you nothing about how it was entered.

[Kenneth Hobson]

If you need that kind of control then you need to add an input means where you can track it.

I have never entered dates by number and I suspect that your users don't either.

[mikemundy]

I have never entered dates by number and I suspect that your users don't either.

Of course they don't.
But the intent of the macro is to permit date entry with or without separators.

So, if the user enters "33148", I want the macro to insert separators and then change the cell value to DATEVALUE("3/31/48") or 17623.

If, on the other hand, the user entered "10/02/1990", which is stored in the cell as
33148, I do not want the macro to change the value of the cell.

I suspect it can't be done.

[martindwilson]

oh i don't know when asked say 'when was d-day?" i immediately say 16229

[mikemundy]

oh i don't know when asked say 'when was d-day?" i immediately say 16229

And I immediately say, or rather want to be able to type, 60741 meaning June 7, 1941. See?

[Kenneth Hobson]

What you might do is to check the NumberFormat for the cell(s). If it is General, then you can reset the numberformat and change the value accordingly. Or, check the value as compared to some cutoff date and then do your magic. Does that help?

[mikemundy]

What you might do is to check the NumberFormat for the cell(s). If it is General, then you can reset the numberformat and change the value accordingly. Or, check the value as compared to some cutoff date and then do your magic. Does that help?

No.

If a macro sees a cell value of 33148,
can the macro determine that the user entered 33148
(intending 3/31/1948) or that the user entered 10/02/1990?

I think not and I guess we can close this now.

Thanks anyway.

[Kenneth Hobson]

If you don't want to do it, then that is fine.

However, if you want to do it as I detailed, set the numberformat to general. When the macro checks it after a Change event for example, it could then do what is needed. This would only work for the first entry in each cell though.

Another method that would work for all entries would be to show a Userform or other dialog to get the information and set it. I would use the sheet's Select event to trigger this. A simple InputBox would let you get the input as a string. It is then just a matter of parsing.

Another idea is to offer the user's two columns to enter dates. A user can then pick the format of entry that they like.

Of course calendar controls are nice ways to control date input as well. A sheet Select event would be good way to trigger this as well.

[royUK]

I doubt very much if a user will opt to figure out the numeric value of a date. maybe they will enter it as 010108 or 010108, in which case Chip pearson has a routine to convert that entry to a date

http://cpearson.com/excel/DateTimeEntry.htm