Calendar

toleafs

Active Member
Joined
Jun 27, 2005
Messages
498
Good Afternoon, here is what I am trying to do.

I have a 2006 Calendar, and a date field formated as follows:
Tuesday, September 4 , 2006.
What I would like to do is run a query that will automatically update the date field to 2007, ie- Tuesday, September 3, 2007.
I tried this by using a -1 against the date field. It gives me the correct Day and Month, However, it does not change the year.
Does anyone have a suggestion? Any help is greatly appreciated.

Thanks
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Sorry I'm a little confused, but that's probably just me.:eek:

Why would subtracting 1, which I assume you are doing, advance a date?

Also September 3 2007 will be a Monday not a Tuesday.
 

toleafs

Active Member
Joined
Jun 27, 2005
Messages
498
I subtract 1 from the date to give me the same day/month for the following year. Example- Today is Wed. OCt 4 2006. If you subtract by 1, the date will be Wed. Oct 3, 2007. But the - 1 does not change the year value, which I am trying to do. Hope this makes sense
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Sorry I'm now even more confused.:eek:

How exactly are you doing this subtraction?

If you have a date field and you subtract 1 then that should return the day before, not a day in the future.
 

toleafs

Active Member
Joined
Jun 27, 2005
Messages
498

ADVERTISEMENT

I cannot load HTML maker...
that's correct, I am returinng the day before, but the -1 does not change the year value in the date field. For instance-
Friday, January 06, 2006 -1 = Thursday, January 05, 2006.
However, I wanted a calculation that would also change the year value. The results should look like this:
Friday, January 06, 2006 -1 = Thursday, January 05, 2007.
 

toleafs

Active Member
Joined
Jun 27, 2005
Messages
498
Thanks for your help, I figured it out...
DateSerial(Year([Run Date])+1,Month([Run Date]),Day([Run Date]))-1
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
If what you want is the SAME day of the week for NEXT year, then adding 1 to the year and subtracting 1 from the day will only work 3 out of 4 times. Every lead year will mess up that calculation. You should check the day of the week before you start this calculation, then after doing the Year + 1, and Day -1 routine, check the day of the week compared to the day of the week before you started. If they are not the same, subtract one more from the Day.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
As Vic mentioned you do have to beware of the leap years - especially whether the adjustment applies before or after Feb 28 too. However, IMO an easier / safer way would be to add 364 days using the DateAdd function. You can see the full syntax for the DateAdd function here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctdateadd.asp

your formula may end up looking like this :
DateAdd("d",364,[Run Date])

And this would automatically take account of the leap years.

HTH, Andrew
 

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,721
Members
410,630
Latest member
JFORTH97
Top