Need a bit more information. When you say until equals 1st January it has to be considered that it may never be equal to the 1st January. Do you want to subtract n until the date is equal or less than 1st January or subtract n as many times as possible without going back past 1st January. eg Subtract 9 from 22nd May 2014 and it will get back to 7th January and the next subtraction will be 29th December 2013.

Then how do you want to display the results. Do you need each date displayed in separate cells or do you simply want the last date when n has been subtracted as many times as possible or maybe you want to display how many times n is subtracted to get the result.

This is for a roster spreadsheet. I have a table (Roster) that has the data.

First column is the roster name, the next 28 columns have either a "D", "N" or white space. The next column has a date (more about that in a minute) and the last column the length of the roster in days.

The main formula is

=VLOOKUP($A5,Roster,IF(DATEDIF(VLOOKUP($A5,Roster,30),E$3,"d")-(VLOOKUP($A5,Roster,31)*INT(DATEDIF(VLOOKUP($A5,Roster,30),E$3,"d")/VLOOKUP($A5,Roster,31)))+Offset<2,VLOOKUP($A5,Roster,31),DATEDIF(VLOOKUP($A5,Roster,30),E$3,"d")-(VLOOKUP($A5,Roster,31)*INT(DATEDIF(VLOOKUP($A5,Roster,30),E$3,"d")/VLOOKUP($A5,Roster,31))))+Offset)

where cell A5 has the name of the roster, E3 thru AI3 has the date as  1/01/2014........31/01/2014 (with the cell format set to "d").

Column 31 of the table is the roster length in days.

Column 30 is "A" date where the roster would have started on January First. This is calculated back from the date the roster was implemented, which invariably is never January First.

This all works fine in the roster, but it is cumbersome working out that date when adding a new roster ( currently has 5 distinct rosters).

What I want to do is add a calculator to the tab with the table on it that, given the length of the roster and the proposed start date, will return the year that the roster would have to have started on.

Example

Roster = 5 on, 5 off, 4 on. So could be counted as a 14 day or a 28 day roster.

Start date is 13 June 2014

The begin date would be 1/01/2009

The formula uses DATEDIF() to calculate the number of days elapsed between 1/01/2009 and whatever date is being displayed.

table column pointer = days elapsed -(roster length*int(days elapsed / roster length))+ offset

The offset is 1 and is there to move the pointer past the name column in the table.

Hope this makes sense

Simply put, decrement a specified date by  "n" days at a time until the date = first of January(year irrelevant, as long as it is before the specified date), factoring in leap years

and not using VBA (I have nothing against VBA, it just has to go on a corporate server and VBA cause's issues).

Thanks in advance

• Moved by Fei Xue - MSFTMicrosoft contingent staff 7 hours 54 minutes ago

Hi Agnemon,

Thank you for posting in the MSDN Forum.

This forum is for developers disscussing the issues about VBA, VSTO and apps for Office. Since the issue is more relate to the end-user, I'd like to move it to Excel IT pro forum.

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thanks for your understanding.

Best regards

Fei