Calculating length of stay of employment WITH PAST DATES

[CV2023]

I am currently using the formula DATEDIF to caculate years/months/days of employment. I am using a workbooks for reporting purposes that is turned in quarterly. The formula currently calculates to "TODAY" instead of an end date cell. How do I specify what "today" is? For example Q2 is April, May, June due end of July and I want to show length of employment on April 30th, May 31st, and June 30th for each month's individual workbook. I have tried searching online for a formula variation but was only able to find formulas where there is an end date cell. These are all current employees so I can't use an end date. Unfortunately because it is a state program the workbooks do have drop down date ranges just no specific date or I would have just used that. For reference here is the formula I am currently using.

=DATEDIF(D14,TODAY(),"y") & " Years, " & DATEDIF(D14,TODAY(),"ym") & " Months, " & DATEDIF(D14,TODAY(),"md") & " Days"

[#2]

@CV2023 Cell E10 links to an external sheet for February 2023. The cell, oddly enough displays the reporting period, Month/Year, to be "February 1 - 28/29" being Month 1 through 28 or 29. without mentioning the Year.

Can't you change the source cell (E9 in the external sheet) to a real month-end date February 28, 2023. E10 will then also be a real date that you can then custom as mmmm/yyyy so that it displays February/2023. Now refer the DATEDIF function to $E$10 rather than to TODAY()

[#3]

Suggestion

You are trying to calculate the employment period up to the present date. You can use the DATEDIF function in Excel to calculate the employment period. The DATEDIF function will return the number of days between two specified dates.

For example, if you have the employment date in cell D14, you can calculate the employment period up to the present date using the following formula:

=DATEDIF(D14,TODAY(),"y") & " years, " & DATEDIF(D14,TODAY(),"ym") & " months, " & DATEDIF(D14,TODAY(),"md") & " days"

This formula will display the employment period in cell D14 in the format "years, months, days".

I agree with you that using the EOMONTH function would be better. The EOMONTH function will return the last day of the specified month. You can use the EOMONTH function in the DATEDIF function to calculate the employment period up to the end of the quarter.

For example, if you want to display the employment period up to the end of Q2, you can calculate the end date of Q2 using the following formula:

=EOMONTH(TODAY(),"Q")
Then you can use the end date of the quarter in the DATEDIF function to calculate the employment period up to the end of the quarter.

For example, if you have the employment date in cell D14 and the end date of Q2 in cell D15, you can calculate the employment period up to the end of Q2 using the following formula:

=DATEDIF(D14,D15,"y") & " years, " & DATEDIF(D14,D15,"ym") & " months, " & DATEDIF(D14,D15,"md") & " days"

This formula will display the employment period in cell D14 in the format "years, months, days" up to the end of Q2.