Solved: Change date in same year

[icthus123]

I need to take a date and use VBA to change it to a different date of the same year. For example I may want to change 01/04/07 to 05/07/07, or 03/02/02 to 05/07/02. Basically I want to be able to change dates to any date I want while keeping it in the same year. There may be some straightforward way of doing this, but I'm not sure how to! Any ideas?

[andrew93]

Do you want a randomly selected date? Or will any other date do? Or a specific date?

Andrew

[icthus123]

Well, different dates! But probably something like 01/04 of the same year. How would I do this?

[andrew93]

Hi

You can convert a date from one to another using something like this in VBA:


NewDate = DateSerial(year(OldDate), 4, 1)
or like this for something apparently (but not) random:

NewDate = DateSerial(year(OldDate), IIf(Month(OldDate) > 6, 12 - Month(OldDate), Month(OldDate) + 6), IIf(day(OldDate) > 15, day(OldDate) - 15, day(OldDate) + 13))
NewDate was declared as a 'Date' variable and changed the value stored in the variable 'OldDate'.

How you then use this depends on what you are trying to do, e.g. if you are wanting to update a value on a form, or in a table etc.

Andrew

[icthus123]

Hi

You can convert a date from one to another using something like this in VBA:


NewDate = DateSerial(year(OldDate), 4, 1) or like this for something apparently (but not) random:

NewDate = DateSerial(year(OldDate), IIf(Month(OldDate) > 6, 12 - Month(OldDate), Month(OldDate) + 6), IIf(day(OldDate) > 15, day(OldDate) - 15, day(OldDate) + 13)) NewDate was declared as a 'Date' variable and changed the value stored in the variable 'OldDate'.

How you then use this depends on what you are trying to do, e.g. if you are wanting to update a value on a form, or in a table etc.

Andrew

Thanks a lot andrew the top one was perfect! I had to basically find out when in a yearly cycle a certain date came, I've used this


dteDate = tbDate.Value

dteStartDate = DateSerial(Year(dteDate), 4, 1)

If dteStartDate < dteDate Then

dteEndDate = DateSerial(Year(dteDate) + 1, 4, 1)

Else

dteStartDate = DateSerial(Year(dteDate) - 1, 4, 1)
dteEndDate = DateSerial(Year(dteDate), 4, 1)

End If