Dateif funciton

[charlene durand]

Hi All, I am looking for a dateif formula for Length of service in position

W14 is the Start Date
X14 is the End Date
AC is the start date of new job title (the dates are only added when the person has changed job titles otherwise it will be blank)

This is my current formula

=IFERROR(IF(DATEDIF($W14,$X14,"y")=0,"",DATEDIF($W14,$X14,"y")&"y ")&IF(DATEDIF($W14,$X14,"ym")=0,"",DATEDIF($W14,$X14,"ym")&" m "),IF(DATEDIF($AC14,TODAY(),"y")=0,"",DATEDIF($AC14,TODAY(),"y")&"y ")&IF(DATEDIF($AC14,TODAY(),"ym")=0,"",DATEDIF($AC14,TODAY(),"ym")&" m "))

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[charlene durand]

Please see attached example

[AliGW]

You have not explained why E8 should be 10y 3m.

There is data missing in the fields referred to in your formula, so how can it possibly calculate an answer?

More detail needed, please.

[charlene durand]

Sorry for the previous attachment. I have added a new one with slight changes but also explanation

[AliGW]

I see no explanation in the new file, and it is much busier, so it's even less clear what's going on.

Please just take a moment to explain, in words and step-by-step, what exactly you are trying to do. We realise it's obvious to you, but it isn't to us.

[charlene durand]

Hope this will be more understandable:

We must report to our department of labour the Length of service in position. Unfortunately, the formula I am using is not correct.

During the year, an employer might have changed job titles so in Column AF – AH this is the movement they are doing so I have added a date they started the Old job title (Column AI) and in Column AG the date which they finished in that position.

1. Date of Engagement (Column W) – this is the date that they start at the company and the current job title unless they have changed job titles but that will be seen in the workforce movement.
2. Termination (Column X) – Date of termination from company
3. Start date of New Job title (Column AC) – This is when an employer has had a job title change.

Therefor I need in Column AD the Length of service in current job title.
I have another column which I have not shown here that gives me the total length of service which is from Date of engagement to termination date.

[charlene durand]

I therefore need the following 3 dates to work out the length of service in current position.

If no movment was done then it would be Date of engatement to todays date and if terminated we would use the termination date instead of todays date.
If momvement then it must be start date of new job title to todays date and if terminated we would use the termination date instead of todays date.

[Roel Jongman]

The iferror in your formula will only trigger if there is no value in X14. I think that is not always the case, the primairy criterium is that if a date is filled in AC then there is a new jobtitle and shorter length of service in current jobtitle

I replaced iferror with a normal if to test if AC14 is blank or not. and I changed the End date = X14 in the dateif functions with an if that will put todays date as end date if there is no date in X14

the formula looks like this

Please Login or Register  to view this content.

[Roel Jongman]

I created a simplified solution after reading the latest explanation. the formula can be shorter and give correct results

(be aware that in Row 25 there is a blank result because the period since start of job and the Terminuation date is less then 1 month..)

Simplified formula

Please Login or Register  to view this content.

please also see attachement formulas applied to rows 24:27

[charlene durand]

Thank you so much this worked

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.