Very specific Calendar Date Table

[lha]

Hi Everyone,

I need help in created a very specific Calendar date table.  Our customer has a calendar that does not correlate to any normal calendars.

For this year (2018):

This calendar starts on November 30, 2017 (Monday), it ends on October 28th, 2018.  There are 13 periods in a calendar year, each 28 day long.

The 2019 year will begin on October 29, 2018. (13 periods of 28 days later).

The begin and end dates for every year changes because the company does not go by a calendar but instead has periods of 28 days for 13 periods and then the new year begins.

How would I create a dyanmic calendar table to adjust the actual calendar date to match this company's dates?

[Greg_Deckler · Accepted solution]

Something is not adding up for me here. See attached PBIX. Are you sure it isn't 11/28/2018 and it is 14 periods of 28 days? Because the cycle of 28 days doesn't repeat on October 28th, the October date of the 28 repeating days is October 31st, not the 28th. See attached PBIX.

[lha]

Hi Greg,

I changed the start date to 10/30/17 (which is when the year 2018 begins for this company).  I think I mis-typed in my help message.

Your formula works! Yay!

However, one additional question, Column 2 (whichs is what I'm reading as the period for the year), does not start over when 13 periods end. It continues into period 14, 15, 16, ... etc. 

Since the mark of the next fiscal year (2019) is when the 13 periods end. How would I update the formula to make it dynamicaly start over at 1 once period 13 occurs? 

Also, how would I create a year column that has the first 13 periods as year 2018, the next 13 periods as 2019, the following 13 periods as 2020.

Thank you so much!

[lha]

Hi Greg,

I changed the start date to 10/30/17 (which is when the year 2018 begins for this company).  I think I mis-typed in my help message.

Your formula works! Yay!

However, one additional question, Column 2 (whichs is what I'm reading as the period for the year), does not start over when 13 periods end. It continues into period 14, 15, 16, ... etc. 

Since the mark of the next fiscal year (2019) is when the 13 periods end. How would I update the formula to make it dynamicaly start over at 1 once period 13 occurs? 

Also, how would I create a year column that has the first 13 periods as year 2018, the next 13 periods as 2019, the following 13 periods as 2020.

Thank you so much!

[Greg_Deckler]

Yep, I stopped working on it when I encountered the issue with the dates. Figured I would clear that up before proceeding. Now that that is cleared up let me see what I can do with restarting the count. Shouldn't be that difficult, basically some kind of Mod 13 essentially, let me see.

[lha]

Hello!

Were you able to figure it out? 

I was able to add a column to figure out the years, every 364 days, the way you figured the periods for 28 days.

I am stuck now on the formula on how to restart the period count after 13 periods and the year count after 364 days. 

I used a rank column to rank the days & then created a manual formula to assign years to between specific ranks. With only 4 years, this was not labor intensive. However, it is still a temporary solution. 

Any help is appreciated.

Thanks!