Free Microsoft Excel 2013 Quick Reference

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?

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?

- Calulate the number of years and months from a specific date
- Count years and months
- Calculate number of years from date
- Calculate number of years
- Calculating number of complete and partial months
- Calculating number of three month periods between two dates...
- Calculating number of three month periods between two dates...
- Formula to caluclate the # of years and months between two dates
- Formulas: Calculating Expiry Date from Years and Mo
- Calculating number of years
- Re: calculating number of days (e.g., Mondays) between two dates
- Trouble calculating age in Years and Months
- Calculate Age In Years And Months
- Help: Return the number of years and months between dates
- Calculate Number of years
- Re: calculating number of days (e.g., Mondays) between two dates
- Dates to be converted into years and months
- Calculate number of years that occur
- Finding the number of Saturdays and Sundays present within a date range.
- Function Fiscal Year and Month
- How do I calculate the number of years, months, weeks and days it has been from a date in A1?
- Function to calculate the number of years, months and days between
- HOW DO YOU CALCULATE AGE BY DATE OF BIRTH I NEED YEARS AND MONTHS
- Calculating number of instances occuring in an hour for 24 hours

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

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

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.

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

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'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.

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.

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

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

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.

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:Just wondering in there is a more efficient way of doing this.=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

Cheers

Richard

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

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!

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?

>>

>>

>>

=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.

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

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

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?

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!

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

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.