Forum Discussion
Calculating length of stay of employment WITH PAST DATES
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 "T...
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.
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.