#1
|
||||
|
||||
Calculate total services of an employee by year, month and days
Hi. I use the below formula to calculate the number of years, months and days that an employee has served in our agency. My problem is how to edit the formula so that the 31 or 30 days should be included in the months, not days.
=DATEDIF([@[First Day of Service]],[@[Last Day of Service]],"Y")&" yrs, "&DATEDIF([@[First Day of Service]],[@[Last Day of Service]],"ym")&" mos, "&DATEDIF([@[First Day of Service]],[@[Last Day of Service]],"md")+1&" days" Thank you. |
#2
|
|||
|
|||
What about adding a day to 'Last Day of Service' instead adding 1 to Datediff() days result? So e.g. instead of calculating Datediff's between January 1st and January 31th and adding 1 for days, you simply calculate all Datediff's between January 1st and February 1st!
|
#3
|
||||
|
||||
You're right Arvi. I added a helper column for the base date of computing the total services. The Last Day of Service remained to avoid confusion as to when an employee's service actually ended.
Thank you. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
appt that occurs every year for 1hr per day for 10 days | scadaman29325 | Outlook | 2 | 07-13-2021 04:58 AM |
Word field last month not working within year change | eduzs | Word VBA | 4 | 07-25-2019 04:31 PM |
Calculate amount of days per month from a given timerange | Yde1203 | Excel | 3 | 05-02-2019 08:13 PM |
Excel day date month year Function | greginky | Excel | 1 | 01-05-2016 02:19 AM |
How to calculate a rolling year-to-date percentage by quarter as the year progresses | sleake | Excel Programming | 2 | 04-23-2015 11:51 AM |