VB to change dates

delaneyjm

Well-known Member

Joined

Apr 22, 2009

Messages

624

• Jun 18, 2010

• #2

Try this formula,

Code:

=IF(A1 < DATE(2010,10,10),date(year(a1),day(a1),month(a1)),A1)

Adjust ranges to suit.

 

Upvote 0

S

superbob

New Member

Joined

Feb 9, 2010

Messages

37

• Jun 18, 2010

• #3

Thanks but the year isn't always 2010 and I need to update the cells directly rather than creating another field.

 

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Jun 18, 2010

• #4

Have you checked the cell format?

Should that date be 6th feb or 2nd June? Bear in mind that different countries use different date formats, if your data is being imported it could be that the source is using mm/dd/yyyy while you're using dd/mm/yyyy. Excel will have no way of knowing which format is desired so will match it to your regional settings.

Seems strange that it's only wrong up to 10/10 though, normally happens on anything where the date could also be the month.

 

Upvote 0

delaneyjm

Well-known Member

Joined

Apr 22, 2009

Messages

624

• Jun 18, 2010

• #5

OKay...my formula just needs a slight modification

=IF(A1 < DATE(YEAR(A1),10,10),date(year(a1),day(a1),month(a1)),A1)

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Jun 18, 2010

• #6

You could try selecting the dates, Data > Text to Columns, press Next twice, tick Date and select DMY then click Finish.

 

Upvote 0

S

superbob

New Member

Joined

Feb 9, 2010

Messages

37

• Jun 18, 2010

• #7

Jasonb75

The cell format is correct. There's a rountine which is run that changes the date if the day is less than 10 or the month is less than 10. Have you seen something like this before?

Delaneyjm,

Thank you for the formula, I will only use this as a last resort due to having creating another field and also have to change the formula.

VoG,

THe solution seems to work but can I macro this to change the format is the day is less than 10 or the month is less than 10.

Cheers

 

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Jun 19, 2010

• #8

Not seen exactly this before, just an observation from copying anything with dates that's in a different format, for example when people post sample data here using Excel-Jeanie. My regional settings use Date format dd mm yyyy, meaning if the sample is in format mm dd yyyy the month and date get switched if the date is less that 13.

Can you give us more info on the routine that is doing this, Maybe it would be better to correct the problem at the source?

 

Upvote 0