Macro to format Dates

Topic

New Reply

I have the dates that when imported, puts it into formatmm/dd/yyyy,where the month is less than 13[/SIZE][/FONT][/COLOR][/SIZE]

[TABLE=”class: MsoNormalTable, width: 609″]
[TR]
[TD=”width: 809, bgcolor: transparent”] I would like a macro, that will put the dates into the correct format i.e dd/mm/yyyy-see sample data attached

Your assistance in resolving this is most appreciated





[/TD]
[/TR]
[/TABLE]

Reply | Quote

Replies

Hi Robert

Thanks for the valuable information.

Regards

Howard

Reply | Quote

Just write a custom Format d/m/yyyy then apply new format to all your cells that have a date.

HTH,
Maud

Reply | Quote

Try this formula
=IF(DAY(B2)>12,B2,DATE(YEAR(B2),DAY(B2),MONTH(B2)))
then format the cell as date

Reply | Quote

Try this formula
=IF(DAY(B2)>12,B2,DATE(YEAR(B2),DAY(B2),MONTH(B2)))
then format the cell as date

That won’t work.

Consider dates such as 12/11/2012 & 11/12/2012.

Presumably Howard has this sorted though as there have been no further replies from him.

Reply | Quote

That won’t work.

Consider dates such as 12/11/2012 & 11/12/2012.

Presumably Howard has this sorted though as there have been no further replies from him.

Quite right I should have said IF(DAY(B2)>13,B2,DATE(YEAR(B2),DAY(B2),MONTH(B2)) )

Unless the OP tells us that he has solved it we have no way of knowing. He may be embarrassed to say that none of the suggestions made are appropriate.

Anyhow, Howard, even if you have solved it a different way, it could be helpful to others to know how you did it.

Reply | Quote

Hi David

Thanks for the help, much appreciated. I managed to solve the problem by using text to column and setting the date to mdy and it worked for me.

Regards

Howard

Reply | Quote