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

@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.

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?

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

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)))

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

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

@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.

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.