Spreadsheet rules adding consecutive years every 30 lines

I don't know how a calendar like that would look. I suppose the calendar template can be reworked so all months have 30 days but I don't know if that suits your purpose or would be useful for anything. I feel it would be very odd. Most days would not correspond with the days in the actual calendar.

There are apparently several variations of ancient 360-day calendars. If you have one in mind, show us what it is.

There are several variations of doing financial calculations based on a 360-day year but none of those financial methods appear to actually use a 360-day calendar, they are adjustments to duration calculations between days. I'm not even sure if the financial adjustments can be made into a 360-day calendar because what you do to translate the actual days into the 360-day calendar depends on both days you are looking at. Sometimes one of the two dates will get adjusted, sometimes both, sometimes neither.

Hi lonebiker,

Start with this. Enter these numbers into Rows 2 and 3 and Select them. Hover the cursor below the selection until you see the yellow dot (the Fill Handle).

Drag the fill handle down to Row 31.

Formula in A32: A2+1

Formula in B32: B2

Select A32 and B32. Fill down.

Continue to Row 361

Good luck with your project.

Regards,

Ian.

Hi again Lonebiker,

Thanks for the green tick and your feedback.

Sorry, I am not understanding the sequence of Jubilee years.

Regards,

Ian.

Hi again Lonebiker,

I think I am beginning to understand.

Seven years times seven repeats (49 years) followed by a year of rest (the Jubilee).

The next cycle of seven years times seven repeats (49 years) starts after the end of the Jubilee year.

Do we skip (or add) a year every 49 years?

You showed the first (Year 50) and second (Year 101) Jubilees. Can you show the third, forth, etc. to help me and other helpers understand the sequence?

Regards,

Ian.

Hi Mathew,

Thanks for the clarification, but I am still unsure! Does the Jubilee occur every 49 years, or every 50 years?

The MOD function might be what you want.

The MOD function returns the remainder from a division. Both arguments are number values.

MOD(dividend, divisor).

Assuming Jubilee is every 50 years, MOD(50,50) equals zero and MOD(100,50) equals zero.

All we need to do is test the year on every row of your table to see if the MOD of 50 is zero.

Here is a very short version of your table:

Formula in C2: IF(MOD(B2,50)=0,"Jubilee Year","")

Adapt the formula for a 49 year cycle.

Please reply with questions or further clarification.

Regards,

Ian.

Hi Mathew,

I am happy to continue. Don't give up - we will find a solution to this problem!

Regards,

Ian.

Hi Mathew,

I should have paid more attention. Jubilee years occur every 49 years.

As an illustration,

This formula will show Jubilee years:

Formula in B2: IF(MOD(A2−1,49)=0,"Jubilee","")

Here is the same table with a filter to show only those rows with "Jubilee" in Column B

If the formula IF(MOD(A2−1,49)=0,"Jubilee","") does not work for you, it may be that your Language & Region settings use comma (,) as the decimal separator. If so, formulas use semicolon (;) as the separator.

The Formula in B2: IF(MOD(A2−1;49)=0;"Jubilee";"")

using whatever your language uses for the MOD function!

Are we getting there?

Regards,

Ian.

Hi Mathew,

There is something going on that I do not understand. Perhaps I should see your document to help me get the full picture.

One method is to place your document on a public website such as Dropbox. Copy the Dropbox link and post it here in a reply. Be aware that anyone reading this discussion will be able to access your document.

Another method is to email your document to me. My email address is ****@mail.com

Before sending your document anywhere, ensure that all personal or sensitive data are removed or made anonymous by substituting made-up data.I suggest that you make a copy first, and then make it anonymous. Send the copy.

Immediately after posting this reply, I shall edit out my email address and update. No-one else is contributing to this thread, so no-one else will receive an email notification of this reply.

Regards,

Ian.

[Email Edited by Moderator]