Age: Formula or vba?

lol.xls

Board Regular
Joined
Oct 5, 2009
Messages
174
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!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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?
 

chinnu_nava

New Member
Joined
Dec 16, 2009
Messages
2
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 No_Of 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

Board Regular
Joined
Oct 5, 2009
Messages
174

ADVERTISEMENT

E3 IS DATE OF HIRE, U1 IS TODAY()
THIS IS THE FORMULA:
Code:
=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

Board Regular
Joined
Oct 5, 2009
Messages
174
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

Board Regular
Joined
Oct 5, 2009
Messages
174

ADVERTISEMENT

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

Well-known Member
Joined
Jan 2, 2009
Messages
828
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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 ? ...)
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
Edit to my Text() format -

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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top