Formula to calculate amount of days/weeks/months between two dates?

MirandaLang

I work in managing long term, in vivo research studies so keeping track of age is important, I am assuming this is a pretty basic formula but I cannot seem to figure it out.

I also want an automatic column that just gives me current age.

Please help!

Jeff Reisman

@MirandaLang

There are numerous ways to do this, depending on what days you want to count.

All days: Just subtract the start date from the end date. =EndDate@row - StartDate@row or alternatively, use NETDAYS function. NETDAYS(start_date, end_date). You can keep a running count by using TODAY(). ex. =TODAY() - StartDate@row gives you the numbers of days since the start date as of today.

Just want to count workdays? Use the NETWORKDAYS function to exclude weekends and designated holidays.

How to count weeks? Utilize the WEEKNUMBER function: =WEEKNUMBER(EndDate@row) - WEEKNUMBER(StartDate@row) or for a running count, =WEEKNUMBER(TODAY()) - WEEKNUMBER(StartDate@row)

Months? You can divide the result of the number of days by 30, or you can use the MONTH function. Keep in mind, MONTH returns the month number (January =1, February = 2, etc) so the result may not really be what you want. For instance if you use =MONTH(EndDate@row) - MONTH(StartDate@row), and your dates are 4/2/22 and 3/31/22, it will say the age is one month when it's just 3 days.

NikkiOno

Can we do this and exclude the year? So just calculate the weeks between a start date and today and leave out the year?

This is what I am looking for and need to convert this in Smartsheets.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/337814#Comment_337814

I think I see what you're trying to do: PTO hours accrue based on annual weeks worked, with the annual week count starting on your hire date each year.

Date calculations in Smartsheet require a valid date value inside a Date-type column. You would need a helper column in order to create the correct date values for your math to work on.

Hire Date Anniversary (date-type helper column): What you want here is to find the most recent instance of your hire month and day. So we'll construct a date value based on whether the hire month and day are greater than or less than the current month and day:

=IF(DATE(YEAR(Today@row), MONTH([Hire Date]@row), DAY([Hire Date]@row)) > Today@row, DATE((YEAR(Today@row) - 1), MONTH([Hire Date]@row), DAY([Hire Date]@row)), DATE(YEAR(Today@row), MONTH([Hire Date]@row), DAY([Hire Date]@row)))

In English: If a date value built from the current year, the hire date month, and the hire date day is greater than today's date, build a date value from the current year minus 1, the hire date month, and the hire date day values; otherwise, build a date from the current year, the hire date month, and the hire date day values.

For "laura" in your example, the Hire Date Anniversary would then be 9/25/21. To get your number of weeks:

=(Today@row - [Hire Date Anniversary]@row) / 7

which results in 43.71 weeks.

Alternatively, if "John" has a hire date of 3/14/17, his Hire Date Anniversary would be 3/14/22, resulting in 19.43 weeks.

Make sense?

Jeff Reisman

@NikkiOno - Hold up, I missed one thing in the formula above. I gotta rebuild it and test real fast, hang on!

Jeff Reisman

@NikkiOno - It's been edited and fully tested!

NikkiOno

The first formula to calculate the anniversary date worked great, but the second formula to calculate the weeks is not working

Jeff Reisman

https://community.smartsheet.com/discussion/comment/337833#Comment_337833

See my revised formula for the anniversary date. I edited my answer above. Use this corrected formula:

=IF(DATE(YEAR(Today@row), MONTH([Hire Date]@row), DAY([Hire Date]@row)) > Today@row, DATE((YEAR(Today@row) - 1), MONTH([Hire Date]@row), DAY([Hire Date]@row)), DATE(YEAR(Today@row), MONTH([Hire Date]@row), DAY([Hire Date]@row)))

NikkiOno

That worked for some but am showing a "-" for others. I don't know what I am doing wrong?

NikkiOno

I figured it out. thanks so much for your help!!

Jeff Reisman

@NikkiOno

Happy to help! When I first saw your ask I thought it was impossible (doing math on date values without years,) but once I figured out what you were trying to do I knew I could find an answer!

NikkiOno

so appreciated! I've been working on this for four days! Thank you again!

NikkiOno

@Jeff Reisman Reisman,

Well, here's another challenge just when I thought I had it figured out ...

I was just told that our PTO goes on a calendar year so it would refresh on Jan 1st of each year and run through Dec 31st.

For example, Nikki accrues PTO from July 1, 2022 (hire date) - Dec 31, 2022 (end of year) and then starts over on Jan 1, 2023.

Laura accrues PTO from Jan 1, 2022 - Dec 31, 2022, because her hire date is before the start of this current calendar year.

How can I make that work?

Jeff Reisman

@NikkiOno 😂 Doesn't that just figure?? LOL

In this case, it's just another sort of IF statement.

=IF(YEAR([Hire Date]@row) = YEAR(Today@row), (Today@row - [Hire Date]@row) / 7, (Today@row - DATE(YEAR(Today@row), 1, 1)) / 7)

In English - If the Hire Date is in the same year as the current year, then subtract the hire date from today's date and divide by 7; otherwise subtract January 1 of the current year from Today's date and divide by 7.

NikkiOno

@Jeff Reisman

That worked! Thanks for your patience! I am new to the company and learning both the company and Smartsheets. You have been SO helpful!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/337886#Comment_337886

My pleasure. Since you're new to Smartsheet, bookmark the links in my signature. The Functions list with links to all the function help pages, and the error messages page are my Smartsheet bible. Also, click on the plus sign + in the left panel on your Smartsheet home page, and search the Solution Center for a template called Smartsheet Formula Examples and download it to your account. It's a sheet with a bunch of examples of formulas that you can interact with and try things out.