MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Date formats


Posted by Stuart on October 07, 2001 8:56 PM

Using the VBA code below, a weekly report asks a user for a date. If the date is entered 5-Oct-01, then this is the text entered in the cell referred to. However, if 5/10/01 (This is the format used in Australia) then 10-May-01 is entered in the cell referred to. Is there a line or two I can add to the macro that will correct this, or should I restrict the format of data entry (how would I do this if that were the case?). Also, I have checked my regional settings, and they are correct.

Many thanks in advance.

WeekEnd = InputBox("What is the week ending date of the report?", ["Report Date"])
Range("G12").Value = WeekEnd


Posted by Juan Pablo on October 08, 2001 7:08 AM

You can add this, but i think you will still have the problem.

WeekEnd = InputBox("What is the week ending date of the report?", ["Report Date"])
If IsDate(Weekend) then Range("G12").Value = WeekEnd

You can also "trick" Excel using the Text function in order to get the proper dd/mm/yyyy format.

Juan Pablo