Help with date formula's

Dana Wanner

I am creating a tracker that will keep track of vacation days, and the number of days an employee gets each year depends on how long they have been an employee. The days are earned each year on their anniversary.

I have almost everything working with the exception of 1 thing.

Let's say I have an employee that started 10/12/2016, as of that date he will get 13 vacation days but not before that date.

Right now I have this as a formula in the cell for Vacation Days Alotted:

=IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 1, 3, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 2, 5, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 4, 10, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 6, 13, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 9, 15, 18)))))

My result is 13 days. Right now since it is not yet 10/12/20 it should only show 10 days. Does anyone have suggestions on what I need to change in my formula to correct this?

Thanks in advance for any help!

Regards,

Dana

Tori Lynn Mayfield

Hi @Dana Wanner,

Great question! In reviewing your formula setup, it looks like any row with a start date listed in 2016 will produce 13 vacation days with your current setup. This is because only the year is taken into account in your function which produces the outcome of 2020 - 2016.

In order to take into account the start day, you may want to instead subtract the whole date from today's date in order to get the number of days between. This can then be divided by 365.25 (the additional .25 is to account for a leap year every 4 years). For example:

• =TODAY() - [Date of Hire]@row) / 365.25

This will produce a decimal, however, that you'll want to use the ROUNDDOWN() function on so that additional days aren't allocated prior to the employee's anniversary, like this:

• =ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25)

This would then replace your existing YEAR(TODAY()) - YEAR([Date of Hire]@row) section in the function resulting in:

• =IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 1, 3, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 2, 5, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 4, 10, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 6, 13, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 9, 15, 18)))))

In my test sheet, using your dates (with an extra row for today's date to ensure it's working as expected), this looks something like:

Note that I've added in the @row function to ensure this function can be added to any row on your sheet and will run more efficiently on the back end. 

Check out these resources from our Learning Center for more on Date functions and using the @row reference in your future formulas:

• https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
• https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates

Hope this helps!

 

Thanks,

ToriLynn

Tori Lynn Mayfield

Hi @Dana Wanner,

Great question! In reviewing your formula setup, it looks like any row with a start date listed in 2016 will produce 13 vacation days with your current setup. This is because only the year is taken into account in your function which produces the outcome of 2020 - 2016.

In order to take into account the start day, you may want to instead subtract the whole date from today's date in order to get the number of days between. This can then be divided by 365.25 (the additional .25 is to account for a leap year every 4 years). For example:

• =TODAY() - [Date of Hire]@row) / 365.25

This will produce a decimal, however, that you'll want to use the ROUNDDOWN() function on so that additional days aren't allocated prior to the employee's anniversary, like this:

• =ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25)

This would then replace your existing YEAR(TODAY()) - YEAR([Date of Hire]@row) section in the function resulting in:

• =IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 1, 3, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 2, 5, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 4, 10, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 6, 13, IF(ROUNDDOWN((TODAY() - [Date of Hire]@row) / 365.25) < 9, 15, 18)))))

In my test sheet, using your dates (with an extra row for today's date to ensure it's working as expected), this looks something like:

Note that I've added in the @row function to ensure this function can be added to any row on your sheet and will run more efficiently on the back end. 

Check out these resources from our Learning Center for more on Date functions and using the @row reference in your future formulas:

• https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
• https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates

Hope this helps!

 

Thanks,

ToriLynn

Dana Wanner

Hello @ToriLynn,

Thank you so much, this is exactly what I was looking for. It works much better all the way around. I really