Excel - Date sum help needed.?

Question

I'm trying to get a formule to show how long somebody has been employed.
I have a column of the start dates.
In the next column I need a formula to work out how long they've been employed.
At the moment I'm using

"=YEAR(NOW())-YEAR(K36) & " years, " & MONTH(NOW())-MONTH(K36) & " months " & DAY(NOW())-DAY(K36) & " days""

This gives me the correct years, 1 month over what it should be, and the days are negative e.g. -24.

Any ideas welcome

Answer 1

You need the DATEDIF function - see link!

=DATEDIF (StartDate,TODAY(),"y")&" years "
&DATEDIF (StartDate,TODAY(),"ym")&" months "
&DATEDIF (StartDate,TODAY(),"md")&" days"

I have had to insert a space between DATEDIF and the opening bracket, or Y!A will truncate the line...

See the link for the explanations...

Answer 2

the elementary contraptions its utilising are DAYS. regrettably for you, day 0 is 0/a million/1900. in case you relatively want years months and days, then you definately will choose a reasonably complicated formulation or macro to repair it. the rationalization is which you will get a distinctive style of days, finding on the initiating and end dates, because of the fact each month has a distinctive style of days in it. the version between a million/a million/ninety and a million/2/ninety is 31 days yet from a million/2/ninety to a million/3/ninety is 28 days, so "a million month " has a variable style of days. can no longer you exhibit it as days ? there is not any ambiguity. Or years with a decimal, alongside with 5.34 years. Or years with a decimal "favourite" month 5 years 6.4 months, the place a month is 365/12 (30.4) days. i know its no longer suitable.

Answer 3

Because year(now()) gives you 2007
month(now()) gives you 12
day(now()) gives you 5

Say they were hired on Aug 14, 2004
you are asking it to show you:
2007 - 2004 = 3
08 - 12 = -4
14 - 5 = 9

What you need to do (and you might have to tweak this some because I don't actually have excel to test it) is use (now() - k36) which if k36 is formatted as a date, then it should give you a date which is equal to the time passed between k36 and now.

so you would say year(now()-k36) & month(now()-k36) & day(now()-k36), at least I think that'll work. Unless now()-k36 returns and integer specifying the number of days that has passed. In which case you have to use \ and mod to convert that to years, months and days.

Answer 4

Let us say you want the answer in yars.
1month is 1/12 year= ca 30.4 day
one day is 1/365 year,

start date: 02.10.2004

end date 07.05.2006

Time is: (7-2)/365+(-5*1/12)+2= 5/365 -5/12+2= 2+ days or months asyou like it.

just work on!

Answer 5

You're trying to make it too complicated, so often it is quite simple. So, if date of start is in cell A5 and today's date is in cell a1 -=today() being the function, to arrive at the number of days someone has been with you its =a5-a1. That will give you number of days. If you want number of weeks then =(a5-a1)/52. I am assuming that to the nearest week is enough.

Answer 6

Since you only want to subtract the month and day from the month or day if today's date value is larger than the given date value, an if-statment can be used to clear this up a bit.

This still does not take into account 30-day months vs 31 day months.

"=YEAR(NOW())-YEAR(K36) & " years, " & IF(MONTH(NOW()) > MONTH(K36), MONTH(NOW()) - MONTH(K36),MONTH(NOW()) - MONTH(K36) + 12) & " months " & IF(DAY(NOW()) > DAY(K36), DAY(NOW()) - DAY(K36), DAY(NOW()) - DAY(K36) + 30) & " days""

Answer 7

it seems to work fine for me.