Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check data item is date not text format
I have a macro which calculates anniversary and other follow up dates from a
given date in a spreadsheet. There is a prompt in the source cell, but users repeatedly enter the date as a string (3rd Aug 07 instead of 3/8/07). I would use a formula like T(A1)=, TRUE,FALSE to check whether the date is in string or date format but how do I achieve this programmatically before sending a message box to the user to change the format? Thanks a lot |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check data item is date not text format
Set up a validation check:
From the Data toolbar menu select: Data - Validation; allow Date from the dropdown And select greater than. You can also add a custom message to specify the type of data required, plus format the cells in a specific manner. Regards, Michael Arch. "Jimbob" wrote: I have a macro which calculates anniversary and other follow up dates from a given date in a spreadsheet. There is a prompt in the source cell, but users repeatedly enter the date as a string (3rd Aug 07 instead of 3/8/07). I would use a formula like T(A1)=, TRUE,FALSE to check whether the date is in string or date format but how do I achieve this programmatically before sending a message box to the user to change the format? Thanks a lot |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check data item is date not text format
Thanks for responding Michael. I understand data validation but the
spreadsheet my macro works on isn't of my design and can't be changed too redaily as you suggest. My macro works on the sheet once it comes to me. What I need to do is verify the date is in the correct format or send a prompt to the user to change it. I imagined using "If . . Then" but can't get the code for the formula I'd use in Excel before generating the message box. "Michael" wrote: Set up a validation check: From the Data toolbar menu select: Data - Validation; allow Date from the dropdown And select greater than. You can also add a custom message to specify the type of data required, plus format the cells in a specific manner. Regards, Michael Arch. "Jimbob" wrote: I have a macro which calculates anniversary and other follow up dates from a given date in a spreadsheet. There is a prompt in the source cell, but users repeatedly enter the date as a string (3rd Aug 07 instead of 3/8/07). I would use a formula like T(A1)=, TRUE,FALSE to check whether the date is in string or date format but how do I achieve this programmatically before sending a message box to the user to change the format? Thanks a lot |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check data item is date not text format
I've now worked out some code which seems to work:
Dim ADate Worksheets("Summary").Range("E4").Activate ADate = ActiveCell If IsDate(ADate) Then Else Response = MsgBox("blah, blah, blah", vbCritical, "WARNING") End If End Sub Is this foolproof or should it be cleverer? Cheers "Jimbob" wrote: I have a macro which calculates anniversary and other follow up dates from a given date in a spreadsheet. There is a prompt in the source cell, but users repeatedly enter the date as a string (3rd Aug 07 instead of 3/8/07). I would use a formula like T(A1)=, TRUE,FALSE to check whether the date is in string or date format but how do I achieve this programmatically before sending a message box to the user to change the format? Thanks a lot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Change Changing Date Format Data to Text | Excel Discussion (Misc queries) | |||
Cell format with Data Import (date appearing as text) | Excel Discussion (Misc queries) | |||
How do I format text as a bulleted item in Excel? Is it possible? | Excel Discussion (Misc queries) | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |