| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 103524 | 2009-09-27 00:27:00 | Excel formula | WayneMiddy (14028) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 814248 | 2009-09-27 00:27:00 | Hi all, I have a list of people and the date they joined our unit what formula would i use so that i end up with a colum in years they have been with the unit? I guess it would be total days to current date /365 to come up with years to one decmial point eg 2.3 years Would like it to update when ever i update the sheet with new data. Thanks Wayne |
WayneMiddy (14028) | ||
| 814249 | 2009-09-27 01:09:00 | I'm a little busy right now but I have a sheet here I can modify to give you Years, Months and Days as output given Join Date and Current Date. In about 2 hours I will come back with a solution for you if no one beats me to it. |
Sweep (90) | ||
| 814250 | 2009-09-27 01:13:00 | No Problem and thanks | WayneMiddy (14028) | ||
| 814251 | 2009-09-27 01:17:00 | BTW the joined date is in coloum K and i have a blank colum to the left (J)for the formula Cheers |
WayneMiddy (14028) | ||
| 814252 | 2009-09-27 01:42:00 | =ROUNDDOWN((TODAY()-datejoined)/365.25,1) Where datejoined would be the figure in column k. Does that work? |
the_bogan (9949) | ||
| 814253 | 2009-09-27 02:20:00 | I must be doing something wrong =ROUNDDOWN((TODAY()-k2)/365.25,1) copied this into J2 the date in K2 is 14/11/92 so i was looking for 16.8 as an display once i get it working i should be able to copy it down the colum thanks Wayne |
WayneMiddy (14028) | ||
| 814254 | 2009-09-27 02:39:00 | I must be doing something wrong =ROUNDDOWN((TODAY()-k2)/365.25,1) copied this into J2 the date in K2 is 14/11/92 so i was looking for 16.8 as an display once i get it working i should be able to copy it down the colum thanks Wayne =ROUNDDOWN((TODAY()-K2)/365.25,1) give a value of 16.8 when K2 is 14/11/1992 worked for me. Is it giving an output in a date format? edit: What version of excel are you using? |
the_bogan (9949) | ||
| 814255 | 2009-09-27 02:52:00 | SBE 2003 and yes i get the date 16/01/00 | WayneMiddy (14028) | ||
| 814256 | 2009-09-27 03:10:00 | Format the cells to number (1 decimal) and you should be fine. | the_bogan (9949) | ||
| 814257 | 2009-09-27 03:30:00 | Try this in cell J2 =DATEDIF(K2,TODAY(), " y " ) & " years, " & DATEDIF(K2,TODAY(), " ym " ) & " months, " & DATEDIF(K2,TODAY(), " md " ) & " days " That will give x years, y months z days. Not decimal I know but may be more informative. |
Sweep (90) | ||
| 1 2 | |||||