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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Thanks for your help, I figured it out...
DateSerial(Year([Run Date])+1,Month([Run Date]),Day([Run Date]))-1
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top