Free Microsoft Excel 2013 Quick Reference

Calculate number of years and months employed using hire date and.

In Excel 2003, I want to calculate the number of years and months employed
using a hire date and the current date. i.e.
06/28/2006 – 06/25/2005 = 11 months
06/28/2006 – 06/25/2004 = 1 year 11 months
06/28/2006 – 08/01/2000 = 5 years 10 months
06/28/2006 – 04/01/2000 = 6 years 2 months

Somebody must have done this before, yes?


Post your answer or comment

comments powered by Disqus
I would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.

Such as someone reaching 40 injury free years in June of this year I can let them know.

Hi,

I have two sets of data in the date format that I need to calculate the number of months and years in between, the second date will always remain the current date I tried using =today() to keep the current date according to the system date and time

eg
15/02/1977 to 11/10/2007
I need to use a formula that will calculate the number of years and months between these dates .
Any help will be appreciated

Clive

I'm trying to calculate number of years from the difference of two dates.

For example:
Column A = 8/18/1997
Column B = NOW() which is displaying the current date ie 07/10/08
Column C = the number of years based on a formula which I don't know how to write

Any suggestions.

the website says to calculate number of years type: =year(A3)-year(A2). when
i type this is i get a weird answer. problem is not that i get the number
signs....i get a date 1900. for example: 1/1/1900 0:00. please advise for
this is for homework, university. thank you to whomever responds.

D L Barnard

Please can anyone help me - I am trying to calculate the number of complete and partial monts in a period. I am using the formula (YEAR(E22)-YEAR(D22))*12+MONTH(E22)-MONTH(D22) but this is giving me 37 months for the period 14/05/2002 to 14/05/2005, I need this to give me 36 months and similarly for 1/07/2005 to 01/07/2005 I need to have the result "1" not zero. This is driving me mad!

Hi,

I've been working on a function to calculate how many quarters there
are between two dates - but not the standard business quarters -
effectively what I am trying to work out is the number of complete
three month periods between two dates.

Example:

Start Date: 27/02/2006
End Date: 19/04/2013

I need to calculate how many complete three month periods have passed
between the two dates - this is not as simple as converting the dates
to a timestamp, defining a year as 60 * 60 * 24 * 365.25 seconds, then
dividing by 4, as the length of months do differ, as henceforth
quarters will differ.

[while the above would work in a lot of cases, it would sometimes fall
due to different lengths of months].

i.e. if you adjust the start dates:

Quarter 1: 1 Feb - 30 April - 89 days
Quarter 2: 1 May - 31 July - 92 days

Therefore, I am trying to figure out how many entire three month
periods has passed between the two dates.

I'm sure this is no specific function that will do, but can it be done
with a combination of other existing function - at the moment it looks
like I'm going to have to write a VBA macro to parse the dates using
lots of IF..ELSE statements.

Also, a quarter (for this purpose) is defined as the first day in the
period to the last day in the period.

Therefore, 1 Jan - 31 March is a complete quarter (for tax purposes),
not 1 Jan - 1 April.

I tried the obvious line:

=FLOOR((DATEDIF(STARTCELL,ENDCELL,"M")/3),1

Using the first set of dates, I get 0, and with the second set I get 1

It is close, but not what I actually am trying to do.

Any suggestions greatly appreciated, as I'm getting quite stumped on
this...

Thanks
Neil.

Hi,

I've been working on a function to calculate how many quarters there
are between two dates - but not the standard business quarters -
effectively what I am trying to work out is the number of complete
three month periods between two dates.

Example:

Start Date: 27/02/2006
End Date: 19/04/2013

I need to calculate how many complete three month periods have passed
between the two dates - this is not as simple as converting the dates
to a timestamp, defining a year as 60 * 60 * 24 * 365.25 seconds, then
dividing by 4, as the length of months do differ, as henceforth
quarters will differ.

[while the above would work in a lot of cases, it would sometimes fall
due to different lengths of months].

i.e. if you adjust the start dates:

Quarter 1: 1 Feb - 30 April - 89 days
Quarter 2: 1 May - 31 July - 92 days

Therefore, I am trying to figure out how many entire three month
periods has passed between the two dates.

I'm sure this is no specific function that will do, but can it be done
with a combination of other existing function - at the moment it looks
like I'm going to have to write a VBA macro to parse the dates using
lots of IF..ELSE statements.

Also, a quarter (for this purpose) is defined as the first day in the
period to the last day in the period.

Therefore, 1 Jan - 31 March is a complete quarter (for tax purposes),
not 1 Jan - 1 April.

I tried the obvious line:

=FLOOR((DATEDIF(STARTCELL,ENDCELL,"M")/3),1

Using the first set of dates, I get 0, and with the second set I get 1

It is close, but not what I actually am trying to do.

Any suggestions greatly appreciated, as I'm getting quite stumped on
this...

Thanks
Neil.

I think the calculation of the number of Years, Months and Days is not as
straight forward as one of the posts has implied. If someone joins on 12/1/05
then
=Month(Now())-Month("12/1/05") will give -1
Similarly negative numbers will be calculated in the day part of the
calculation.

I would suggest splitting up the calculation into three steps

If A2 has the join date then put formula for Year in B2. This will be
=IF(AND(YEAR(NOW())>YEAR(A2),MONTH(NOW())>MONTH(A2 ),DAY(NOW()>DAY(A2))),DATEDIF($A$2,NOW(),"y"),0)

Put formula for calculating Months in C2. This will be
=DATEDIF(DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)),NOW() ,"m")

Put formula for calculating Days in D2 as follows
=DATEDIF(DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)),NO W(),"d")

There is probably a simpler approach. I would definately like to hear about
it.

"Christyepd" wrote:

> I am setting up the employees lenths of service from the date hired until
> present date.

I need a formula to work out an expiry date from today, using a given number of years and months.

For example, today is 15th July 2003, using 5 years and 5 months I would want December 2009 to be returned. Using 0 years and 1 month I would want August 2003 to be returned.

At the moment I am using something like:

=TODAY+(Years*365)+(Months*30)

It works fine - but it's not 100% accurate as there aren't 30 days in each month, and sometimes there are 366 days in a year. Does anyone know a better way to do this.

Example file attached to help.

Thanks for looking.

Ted

I need to calculate the number of years between 2 dates. The dates are in 2 separate cells. An example would be the number of years between 11/01/99 and 8/21/03, which is roughly 3.67. How do I calculate this in a formula?

Hi!

Actually, I've thought about the June 5 2079 row limitation many times but I
personally have never had to come up a formula that projects that far into
the future so I never bothered looking into a fix.

Maybe in one of the future versions of Excel MS will increase the number of
rows and solve that limitation for us!

Cheers

Biff

<Robert_Steel@nospam.com> wrote in message
news:OqIz9jFiFHA.2072@TK2MSFTNGP14.phx.gbl...
> Biff
> I like the use of the Row() function as you have used it in array
> formulas.
> However, and risking looking a bit picky...<g>
> when used in dates we have an iminent Y2K situation on the 5 June 2079.
> (the date value of the last row)
>
> A small fix you could employ
> SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("1:"&B1-A1)),2)={1,2}))
> or in your final formula
>
> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(N3-1+ROW(INDIRECT("1:"&O3-N3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>
> just using the Row() to increment the date not be the date.
>
> This is still not perfect as you can only have dates approx 179 years
> apart.
>
> hope it's of interest
> RES

The attached is my effort at calculating the age of the equipment in column A.
That is, how long between the date in column B and 'today' expressed in years and months.
I have a decimal year adnd days, but these are not what I need.

As ever, any help on this is appreciated.

John.

Hi all,

I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.


	VB:
	
=TRUNC((B1-A1)/365.25)&" Years "&ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Just wondering in there is a more efficient way of doing this.

Cheers

Richard

Seems simple but I just cannot get this to work. Have 2 date cells, Lease Starts, Lease ends. Need to return how many years, months & days between these dates, rounded up. Example: Lease Start: 23 Mar 2007; Lease Ends 27 April 2008. Result should be: 1 Year 1 Month 5 Days.

SO always round up years first, then count months and remining days.

I am trying to calcualte number of years an employee has been with the
company from today's date. I can't seem to find a formula that will help me
with that.

Also, I need a formula that will calculate 75 workdays from date of hire.

Thanks!

Hi!

Let's see if we understand you.....

Columns G, H and I, may or may not contain the letter abbreviations for the
days of the week.

>G3 H3 I3 N3 O3 Q3
>M F (blank) 10/9/05 12/15/05 (answer)

So, based on your example above, you want to count the Mondays and Fridays
between 10/9/2005 and 15/15/2005.

Is that what you want?

Try this.....

Just make sure that the abbreviations you use match what are in this formua:

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
> This is great, but it doesn't factor the variability of the days tried to
> explain in my initial query. Data in columns G, H, and I will be days of
> week, but will change from row to row (as will the respective dates in
> columns N and O). Sometimes there will be no data in H and/or I. I am
> hoping to create something where a clerk can put in the days of the week
> and
> the spreadsheet formula will count up the days. Note: the number of days
> will then be used in another formula to calculated $$.
>
> Perhaps if I show you what I have so far will help:
>
> G3 H3 I3 N3 O3 Q3
> M F (blank) 10/9/05 12/15/05 (answer)
>
> I tried Bernie's formula but that didn't work. I had to change cell
> references, and I don't understand the formula (and cannot decipher Excel
> Help's explanation) so that might be why.
> I am currently using IF formulae to reference columns G, H and I into the
> weekday_number, but think I have exceeded the quantity of formulae one can
> put in a single cell.
>
> If you have an answer, WHAT A RELIEF!!!
>
> THX.
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> After seeing Bernie's post I'm wondering if I understand the question!
>>
>> To count specific weekdays between 2 dates (inclusive):
>>
>> Where Monday = weekday 1 and Sunday = weekday 7
>>
>> Start date in A1
>> End date in B1
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>>
>> To count more than 1 weekday like Mondays and Tuesdays:
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>>
>> Biff
>>
>> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> wrote
>> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>> >I need to count the number of days between a start date and end date and
>> >the
>> > questions about this I found here don't seem to help (boggle me!) I
>> > need
>> > a
>> > universal formula that will cover variable days which are listed in a
>> > separate column:
>> > G (rows) = names of days; J (rows) = start date; K (rows) = end date.
>> > On
>> > top of this, I am sometimes having to count more than one day (up to
>> > three)
>> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put
>> > in
>> > separate columns (i.e., columns G,H,I) if needed, but if only one
>> > column
>> > has
>> > a day listed, the formula needs to ignore the empty columns.
>> > A knotty problem?
>>
>>
>>

I am having the data in respect of dates, I want the following to be findout by existing the dates, for a future date as on 31-05-2010, or as on 30-06-2010. i.e,. the dates to be converted into years and months from the existing dates. I have got the result by using the following formula which one of our smart, intelligent person who has given in this type of forums.

=DATEDIF(A2,TODAY(),"y")
=DATEDIF(A2,TODAY(),"ym")

Years and months should come in a separate columns. and taking the existing dates for future date.
in the mentioned place instead of "Today" I am putting the date as 31-05-2010, or 30-06-2010, but I am not getting the desired/required result. Please help.
Advance sincere thanks to the solution provider.

Hello all

Let's say I have two dates, 1st October 2010 and 1st September 2012. I want to count the number of years, including the year of the start date. So I want a formula that is going to give me 3, because there are 3 years, 2010, 2011 and 2012. Anyone have any ideas?

Thanks

Morning,

I have been agonising over this problem for a couple of days now, and anyone who could help to put me out of my misery I would be forever indebted to.

I am trying to create a spreadsheet to calculate labour costs for a project. My problem arrises due to the guys being paid time and a half on a Saturday and double time on a Sunday. The spreadsheet user is supposed to be able to specify the start and end date of the project, and then it would automatically calculate the number of Saturdays and Sundays present in the project date range. I know you can find the number to labour days easily enough, but is there a way to find the number of Saturdays and Sundays individually?

For example, if the project began on 05/09/08 and finished on 20/09/08 the would be 3 Saturdays and 2 Sundays present. However, if the project was posponed to start on 07/09/08 and finish on the 22/09/08, then there would be 2 Saturdays and 3 Sundays.

Thanks

Hello,

Can anybody help me with a VBA function that willl calculate Fiscal Year and month.

Considering that the date is in column A in the format 5/27/2010 I need in column C to be returned FiscalYear/FiscalMonth 11/02

(Fiscal Year: April - March)

Thanks a lot!

How do I calculate the number of years, months, weeks and days it has been from a date in A1?

Hi,
Hope you can help. I am trying to find a Function to calculate the number
of years, months and days between 2 dates. Such as to calculate how old
someone is down to the day.

I have played with the YEARFRAC fx and the following:
=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)

I know I am missing something. Can you help?

Thanks so much!

How do you calculate age by a birth date I need years and months... if the
person is not a year old I need to know how many months... if the person is
1yr 2mo I need it to return 1yr 2mo... if the person is only 2mo I need it to
return 2mo.. is there any kind of formula that could work...

Thanks Donna

I am trying to calculate number of customers who arrive every hour in a
day for theentire year.

suppose: the computer records the name and info of a person arrived and
stores the time he/she arrived like:

10:27 xyz
10:29 abc
10:45 123

so I want to calculate the total arrivals in an hour from 10:00-10:59
and I want to that for the entire 24 hour period and then for the
entire month for each hour,

Please let me know how could i do it in a faster way using exfel.

Thanks,
RP!


No luck finding an answer? You could always try Google.