Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-06-2022, 08:31 PM
Marcia's Avatar
Marcia Marcia is offline Calculate total services of an employee by year, month and days Windows 10 Calculate total services of an employee by year, month and days Office 2019
Expert
Calculate total services of an employee by year, month and days
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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.
Attached Files
File Type: xlsx Service.xlsx (11.3 KB, 7 views)
Reply With Quote
  #2  
Old 03-07-2022, 12:12 AM
ArviLaanemets ArviLaanemets is offline Calculate total services of an employee by year, month and days Windows 8 Calculate total services of an employee by year, month and days Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #3  
Old 03-07-2022, 01:58 AM
Marcia's Avatar
Marcia Marcia is offline Calculate total services of an employee by year, month and days Windows 10 Calculate total services of an employee by year, month and days Office 2019
Expert
Calculate total services of an employee by year, month and days
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
Reply

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
Calculate total services of an employee by year, month and days 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft