Extract years between two dates

[koolwaters]

Hi,

I need to extract the years between two dates.

For example:

Start Date 2010-09-07
End Date 2014-08-30

The number of years between the Start Date and End Date are four.

As part of my query result, I need to see only the years between these two dates. So I would see,

2011 2012 2013 2014

If I have
Start Date 2010-09-07
End Date 2013-08-30

I need to see 2011 2012 2013

I hope that someone can assist.

Thanks in advance.

 

[Montez659]

Not sure quite what you are looking for. Is there another variable to determine Start Date and End Date? If so, wouldn't simply adding to the criteria of your field Start Date something like >startVar and in your End Date criteria having <endVar?

Is that what you are looking for or am I way off?

 

[koolwaters]

I am not sure I follow, but what I want are the calendar years between my Start and End Dates.The Start and End Dates vary, so I am not sure how to accomplish that.

 

[Scott Huish]

In B4:
=IF(ROWS($A$1:A1)<=DATEDIF($B$1,$B$2,"y")+1,YEAR($B$1)+ROWS($A$1:A1),"")

Copy down.

Excel Workbook
	A	B
1	Start Date:	9/7/2010
2	End Date:	8/30/2014
3
4		2011
5		2012
6		2013
7		2014
8
9
10
Sheet1

 

[koolwaters]

Hi HOTPEPPER,

I am trying to acheive that result in Access and would need my years in columns.

 

[Scott Huish]

Sorry, didn't see that this was an Access problem.

 

[Montez659]

I am still having a hard time following what you need. You said this is a query but do you need it for a report?

Try to lay out the whole scenario of what you are trying to do. I am having a real hard time picturing what you are looking for.

 

[koolwaters]

Hi Montez659,

I am trying to create a budget forecast report as detailed in the post below.

http://www.mrexcel.com/forum/showthread.php?t=537412

I have not received any replies to the post and am still trying to tackle it.

This is a sample of what I am trying to acheive in an Access report but creating the query is a challenge. The years are not captured, only the Start Date and End Date.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Dept	Disclipine	Course	Duration	2010	2011	2012	2013	2014	2015	Amount Requested	Approve/Decline
2	Jane Doe	3	1	MBA	2	10	10					20	Approved
3	John Doe	2	9	BSC	6	10	10	10	10	10	10	60	Approved
Sheet1

Excel 2007

Cell Formulas
Range	Formula
L3	=SUM(F3:K3)

I hope this helps to explain what I am trying to acheive.

 

[Montez659]

Is this sheet in a report?

I guess what I am not understanding is what exactly are you querying for if all you have is the beginning date and ending date?

 

[Montez659]

Ok, I see from your link (that I should have read completely) that you are looking for a report and not necessarily a query.

When you are forecasting for the future years, are there some records that would start in future years (i.e., they start in 2013 but they are in the db as of today)? Also, what is the maximum number of years that you want to forecast for?

I am going to have to do some thinking about this, considering I saw your original post a while back and passed on it cause I thought it was above my head.