View Full Version : Excel formula

WayneMiddy

27-09-2009, 01:27 PM

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

Sweep

27-09-2009, 02:09 PM

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.

WayneMiddy

27-09-2009, 02:13 PM

No Problem and thanks

WayneMiddy

27-09-2009, 02:17 PM

BTW the joined date is in coloum K and i have a blank colum to the left (J)for the formula

Cheers

the_bogan

27-09-2009, 02:42 PM

=ROUNDDOWN((TODAY()-datejoined)/365.25,1)

Where datejoined would be the figure in column k.

Does that work?

WayneMiddy

27-09-2009, 03:20 PM

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

the_bogan

27-09-2009, 03:39 PM

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?

WayneMiddy

27-09-2009, 03:52 PM

SBE 2003 and yes i get the date 16/01/00

the_bogan

27-09-2009, 04:10 PM

Format the cells to number (1 decimal) and you should be fine.

Sweep

27-09-2009, 04:30 PM

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.

the_bogan

27-09-2009, 04:36 PM

Sweeps answer is much prettier than mine :)

WayneMiddy

27-09-2009, 04:41 PM

It works thank you so how do i copy the formula down the colum so the k2......22 is in each row with out having to do it manualy

Cheers

Wayne

the_bogan

27-09-2009, 04:47 PM

It works thank you so how do i copy the formula down the colum so the k2......22 is in each row with out having to do it manualy

Cheers

Wayne

Hover your mouse over the bottom right of K2, and then drag it down.

WayneMiddy

27-09-2009, 04:49 PM

Thanks sweep but i prefer bogan's formula just need to know when people are comming up to 5, 10, 15 20 years service etc.

Cheers

Wayne

WayneMiddy

27-09-2009, 05:06 PM

I am learning all the time, thanks guys i have used Lotus 123 years ago and excel is different should probably book myself on a course.I Will try sweeps formula in another colum to see if i need it.

Thanks

Wayne

Sweep

27-09-2009, 05:18 PM

Thanks sweep but i prefer bogan's formula just need to know when people are comming up to 5, 10, 15 20 years service etc.

Cheers

Wayne

No worries. I thought you could later do a sort on J2 to see who had been hanging about the longest.

Did you get the copy down OK?

WayneMiddy

27-09-2009, 05:23 PM

Next question so where i dont have a joined date how do i stop it comming up with 109.8 years iam gussing excel is starting at year 1900 before we had computors.

Thanks

Wayne

Sweep

27-09-2009, 05:38 PM

Next question so where i dont have a joined date how do i stop it comming up with 109.8 years iam gussing excel is starting at year 1900 before we had computors.

Thanks

Wayne

How many people don't have a date joined in cell K2?

There is no point in doing the calculation if date joined is blank.

For those that don't have a date joined simply remove the formula in Jxx.

Or modify the formula to include an IF function.

WayneMiddy

27-09-2009, 05:43 PM

Hi Sweep yes i have thought of that, just so you know what i am doing i am crew manager for a coastguard unit 35 crew (Rows) 28 headings (Colums) a bit of a handful plus it needs updating, I was thinking of adding a new sheet for each crew member then we can track things like training, medical, qualls, add a photo etc and yes somebody going to say i should use Access but i dont have it and would know how to use it.

All sugestions and help appreciated

Cheers

Wayne

the_bogan

27-09-2009, 05:52 PM

=IF(K2<>"",ROUNDDOWN((TODAY()-K2)/365.25,1),"No join date")

or similar. This'll work only if K2 is blank.

Please be aware my excel knowledge is self taught, so there's probably a much cleaner way of doing it.

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.