Age: Formula or vba?

[lol.xls]

I have a worksheet which calculates the amount of time an employee has been with our company. I currently use datedif, but i am unable to sort properly using this method. Is their a way to change the output using datedif from m,d,yyyy to mm,dd,yyyy? If not, anyone have a vba solution? Thanks for the help!

 

[xenou]

Can you provide an example of your input/output with datedif?

Dates should sort by their "internal" serial number, so the displayed format should make no difference. If you format the cells to "general" format you should see a number such as 40163 (which is Dec 16, 2009 in numeric serial format).

Note: are you sorting on more than one column? Perhaps there is another sort field that is taking precedence?

 

[Excelestial]

If you're calculating the amount of time...why do you want it in a date format?

 

[chinnu_nava]

I have a worksheet which calculates the amount of time an employee has been with our company. I currently use datedif, but i am unable to sort properly using this method. Is their a way to change the output using datedif from m,d,yyyy to mm,dd,yyyy? If not, anyone have a vba solution? Thanks for the help!

Hi,

Just try this formulae.....

=Today()-Select the joined Date...This will give you the Nf days...

Dont forget to format the cells...RIght CLick---->Format cells----->Number and Decimal Places is 0...
..Please let me know if this is not working out for you.....

 

[lol.xls]

E3 IS DATE OF HIRE, U1 IS TODAY()
THIS IS THE FORMULA:

=IF(X3=0,(DATEDIF(E3,F3,"y")&" years, "&DATEDIF(E3,F3,"ym")&" months, "&DATEDIF(E3,F3,"md")&" days"),(DATEDIF(E3,$U$1,"y")&" years, "&DATEDIF(E3,$U$1,"ym")&" months, "&DATEDIF(E3,$U$1,"md")&" days"))

WHICH OUTPUTS:
0 YEARS, 1 MONTH, 2 DAYS

WHEN I SORT THE COLUMN WHICH CONTAINS THE TIME WITH OUR COMPANY SOMEONE WITH 11YEARS IS SORTED BENEATH SOMEONE WITH 2YEARS WITH OUR COMPANY.

 

[lol.xls]

I just seems the most appropiate way to see how long our employees have been with our company.

John smith 2 years, 6 months, 22 days

 

[lol.xls]

can you provide an example of your input/output with datedif?

Dates should sort by their "internal" serial number, so the displayed format should make no difference. If you format the cells to "general" format you should see a number such as 40163 (which is dec 16, 2009 in numeric serial format).

Note: Are you sorting on more than one column? Perhaps there is another sort field that is taking precedence?

no, just one column

 

[Excelestial]

Why not first allow a column to calculate the number of days. Sort on that column using the RANK function. Adjacent to the sorted column is the column that formats the number of days into years, months and days.

 

[xenou]

Try:
=IF(X3=0,(TEXT(DATEDIF(E3,F3,"y"),"##")&" years, "&TEXT(DATEDIF(E3,F3,"ym"),"##")&" months, "&TEXT(DATEDIF(E3,F3,"md"),"###")&" days"),(TEXT(DATEDIF(E3,$U$1,"y"),"##")&" years, "&TEXT(DATEDIF(E3,$U$1,"ym"),"##")&" months, "&TEXT(DATEDIF(E3,$U$1,"md"),"###")&" days"))

assumes no one works more than 99 years with your company!

EDIT: Note, Prefer Excelestial's idea (but do we need RANK() ... just sort by number of days ? ...)

 

[xenou]

Edit to my Text() format -

For the years and months, use:
00 instead of ##

and for the days use:
000 instead of ###