Every January increment year by 1

[jschlapi]

Hello,
I have a spreadsheet where I would like to increment the year by 1 every January. The user inputs the 3 month JAN (or any other month) in cell A7. The current year is captured in cell B1. Then there is a concatenate function that will copy the dates all the way down column B. The reason is, column E has a formula that will figure out the Weekends & "X" them out. Here is my formula for Column B:

=IF($A$7="Jan",CONCATENATE($A$7," ",$A9,", ",$B$7),CONCATENATE($A$7," ",$A9,", ",$B$1))

My problem is if I type in FEB I it will revert back to last year.

So, I think my best bet is that when I type JAN the dates down column B will increment the year by 1. (December's report is completed by January 15th, so once I pass that date, everything will be current year).

Hope this makes sense. Thank you for your ideas.

 

[Scott Huish]

There is a function called WORKDAY that can automatically ignore weekends.

What kind of list are you actually trying to obtain? Can you provide a sample?

 

[FDibbins]

Also, it is almost always better to use real dates when working with dates. To excel, Jan is just 3 letters, not a date, but if you enter an actual date - 1/1/2017 or =TODAY() - for instance, excel can use those to work out whatever other dates you need.

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Mon 28 Aug 2017) is actually 42975

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So if you have a (real) start date in a cell, you can use something like =a2+1 to get the next day. To ID weekends, you could use =if(=WEEKDAY(A2,1)>5,"weekend","weekday") (change weekday/weekend as needed)

 

[jschlapi]

Thank you