Dates in VBA

Resolved · Medium Priority · Version 2016

Wed 10 Jan 2018, 14:40 Delegate Michael said...

Michael has attended:
No courses

Dates in VBA

Currently I have a macro which runs a filter to look for all rows in the sheet which are before the date entered in cell E11 on a different sheet.

The code works perfectly but it is using an American date format (MM/DD/YYYY) despite the cell in question being formatted as DD/MM/YYYY. Is there a way to change this so that the content of the cell can be entered in UK format and VBA will interpret it as such?

Wed 10 Jan 2018, 15:10 Trainer Jens said...

RE: Dates in VBA

Hi Michael,

Thank you for the forum question.

Dates in many tools in VBA can be a nightmare. Yes many tools turn them to US dates.

try: format(range("E11"),"dd/mm/yyyy")

The CDate function is another option

= CDate("TheUsDate")

I have wasted a lot of time struggling with the same issue. I have always been able to get it right but with different tools and solutions.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 17 Jan 2018: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

The dual nature of toolbar buttons

Many toolbar buttons are dual purpose, though the two purposes are often linked in some way. For example, Align Left aligns a cell's contents to the left of the cell. However, hold down Shift and press the Align Left button: Excel aligns the cell contents to the right.
You may respond: So what? Well, you can reduce the number of buttons on your toolbar to make your screen less cluttered and allow more room for, perhaps, some of your own commands. After all, what's the point of an Align Right button when Shift+Align Left does the same thing?

View all Excel hints and tips