how can i sync dates in a column with formula?

marcusben

Hello,

below, I will explain to you the calculation of the termination notice period before the expiry of a contract and the sending of the reminder e-mail to the responsible person according to this notice period;

1- In the first step, I enter the end date of the contract and the notice period on a daily basis.

2- The "Deadline for Notice of Termination" field has been formulated according to the notice period. According to the situation in the example, it will send a reminder e-mail to the responsible person 6 months in advance.

The formula in the "Deadline for Notice of Termination" field is as follows;

=[Current Contract End Date]@row - [Termination Notice Period (Days)]@row

So far everything is normal and working. (notifications etc.)

If you notice, the contract expiry date is 5.8.2024. Since 6 months notice is requested, the date is expected to be 5.2.22024.

but the date is 7.2.2024.

I tried removing the weekends to fix this situation. but then it crushes the formula in the "Deadline for Notice of Termination" column.

I would be very happy if you could support me in this matter.

thanks

Kelly Moore

Hello @marcusben

The difference of dates is because all months don't always have 30 days in them.

Here's one approach that will force the day to equal the Contract end date. This works if you are working in whole month increments. If, for example, the notice was going out 45 days earlier then the formula might not make sense. We could adjust it to make it more robust but it would make it more complicated - and it would never be 100% accurate.

=IF(DAY([Current Contract End Date]@row) = DAY([Current Contract End Date]@row - 180), [Current Contract End Date]@row - 180, DATE(YEAR([Current Contract End Date]@row - 180), MONTH([Current Contract End Date]@row - 180), DAY([Current Contract End Date]@row)))

This looks at the day to see if the subtraction product ends up with the same day. If yes, simply do the subtraction. If no, reconstruct the date value so that it will match.

Kelly

Kelly Moore

Hello @marcusben

The difference of dates is because all months don't always have 30 days in them.

Here's one approach that will force the day to equal the Contract end date. This works if you are working in whole month increments. If, for example, the notice was going out 45 days earlier then the formula might not make sense. We could adjust it to make it more robust but it would make it more complicated - and it would never be 100% accurate.

=IF(DAY([Current Contract End Date]@row) = DAY([Current Contract End Date]@row - 180), [Current Contract End Date]@row - 180, DATE(YEAR([Current Contract End Date]@row - 180), MONTH([Current Contract End Date]@row - 180), DAY([Current Contract End Date]@row)))

This looks at the day to see if the subtraction product ends up with the same day. If yes, simply do the subtraction. If no, reconstruct the date value so that it will match.

Kelly

marcusben

Hello @Kelly Moore ,

you said;

The difference of dates is because all months don't always have 30 days in them.

Yes, I've thought about this. I just didn't write. The following formula sought to work. so thanks

I just made a small change;

I added -"Termination Notice Period (Days)" instead of 180.

because in some cases instead of 180 it's 270, 320 etc. it could be.

have a nice day

marcusben

Hello @Kelly Moore ,

I have identified some situations, i would like to share for your help;

1- When a warning is requested 6 months (180 days) before the contract will expire on 31 December, July is calculated instead of June. I thought because my guess is that June doesn't turn 31 here. but when this happens, it is delayed by 1 month. This means that the reminder mail is delayed.

2- I have determined the termination notice period of 720 days for the contract that will expire on 21.2.2024. I expected it to return to 21.2.2022, but it does 21.3.2022. If I log in as 730 days (exactly 2 years) then it gives the correct date. (not on the weekend)

When we look at example b, I see that it has provided the correct return for 720 days.

example a:

example b:

3- In this example I expect it to go to March 30th. but I'm not sure if it was postponed by 1 month because it coincided with the 30th of March weekend.

thanks for your help

Kelly Moore

Hey Marc

Sorry for the delay. This took a bit to work out. There are numerous posts regarding how to add months - it gets complicated since one has to account for the changing year value, something that is automatically managed when straight days are added to a date. We can give this a go to see if it gets you closer. There are many functions that smartsheet does very well and there are some other functions that are still developing. The function of Time, in general, is still a developing function within smartsheet.

I added two new helper columns - we could incorporate these formulas into a single formula perhaps in the end, but whenever I have a complicated formula I try to break the formula into steps so I can watch the failure modes more closely. The two helper columns are for this purpose.

[Number of Months]

=INT([Termination Notice Period (Days)]@row / 30)

[Number of Years]

=INT([Number of Months] + MONTH([Start Date]@row)) / 12)

The INT() function will take a value that might have a decimal in it to a whole number.

Once the helper columns are added, let's try this formula. The formula looks first to see if the dates cross years. If yes, then the years and months are adjusted. If no, it simply adds months together.

=IF(OR(MONTH([Start Date]@row) + [Number of Months]@row > 12, [Number of Months]@row >= 12), DATE(YEAR([Start Date]@row) + [Number Years]@row, ((MONTH([Start Date]@row) + [Number of Months]@row) - ([Number Years]@row * 12)), DAY([Start Date]@row)), DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Number of Months]@row, DAY([Start Date]@row)))

Will this work for you?

Kelly

marcusben

Hey @Kelly Moore ,

thank you for your last formul... I encountered 2 different problems here;

1-

The "Deadline for Notice of Termination" field must be smaller than the "Start date" field. it should be calculated according to the value entered in the "Termination Notice Period (Days)" column.

For example, as seen on the picture

"Termination Notice Period (Days)" = 60 days

so

The date in the "Deadline for Notice of Termination" field should show 60 days ago.

2- When I enter 240 days or more in the "Termination Notice Period (Days)" field, the formula breaks down as seen in the picture below. Normally according to the contract information I get here 720, 900 days etc. can enter.

really thank you so much

Marcus

Kelly Moore

Hey Marcus

Let's see how close we can get to what you want. I'll mention again that because we're making some assumptions on number of days in a month and/or number of days in a year, we will never be completely accurate.

I found a mistake in what I gave you as Number of Years - hopefully you had already corrected the missing parenthesis.

=INT(([Number of Months]@row + MONTH([Start Date]@row)) / 12)

Once that was fixed, this original formula works for me on all dates*

=IF(OR(MONTH([Start Date]@row) + [Number of Months]@row > 12, [Number of Months]@row >= 12), DATE(YEAR([Start Date]@row) + [Number of Years]@row, ((MONTH([Start Date]@row) + [Number of Months]@row) - ([Number of Years]@row * 12)), DAY([Start Date]@row)), DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Number of Months]@row, DAY([Start Date]@row)))

But, I think I became confused what date you are looking for. *My formula is adding the number of days (Termination Notice Days) onto the Start date. Do you mean this value should be subtracted from the start date?

This is where I had your formula as I tested it

Please clarify for me what columns are added and/or subtracted and from what days.

Kelly

marcusben

Hello @Kelly Moore,

thanks for your support.

I want to clear up a misunderstanding. Maybe there was a mistake in my statement. The field i call "Start Date" is actually the current end date of the contract. This field triggers the "Deadline for Notice of Termination" field. so ;

The "Deadline for Notice of Termination" field should be small in the "start date" field. accordingly, if 90 is entered in the "Termination Notice Period (Days)" field, as in the example below, i need to see 90 days before the date of 31.5.2022 in the "Deadline for Notice of Termination" field. like february 2022... etc

thanks

Marcus

Kelly Moore

Hey Marc

Have you tried the Workday() function?

=Workday([Start Date], 0-[Termination Notice Period (Days)]@row)

does this do what you want?

Kelly

marcusben

Hi Kelly Moore,

Sorry these studies didnt work for me... :(

thanks, have a nica day

Marcus