check if a date falls within a specific month/year

[Kmitchell]

I'm looking for a formula to help me define if a date falls within a specific month and year.

In column B sits the current month and year (December 2010 which will vary with each month). And in column G sits the termination date. I want to say if(g1 falls in the month and year as b1, then give me a 1, otherwise give me a 2. I have tried multiple formulas and I'm not getting it to work properly. Is it the dated if function?

 

[njimack]

Assuming your cells contain actuals dates (e.g 01/12/10) and not text ("December 2010"):
=IF(AND(YEAR(G1)=YEAR(B1),MONTH(B1)=MONTH(G1)),1,2)

 

[Kmitchell]

perfect! Thank you!

 

[cbcrocker]

Assuming your cells contain actuals dates (e.g 01/12/10) and not text ("December 2010"):
=IF(AND(YEAR(G1)=YEAR(B1),MONTH(B1)=MONTH(G1)),1,2)

Similarly, how would you do this if you were looking for dates within column G that fell within the previous month than the month the report is being created? For instance I'm creating a report in the month of May which will look at April's data.

Keep in mind that the creation date and the dates in column G may span years ... for example, running report on 01/01/2015 for looking for data from 12/01-31/2014.

 

[Rick Rothstein]

Similarly, how would you do this if you were looking for dates within column G that fell within the previous month than the month the report is being created? For instance I'm creating a report in the month of May which will look at April's data.

So instead of having a date in cell G1, you want to use "today's date" instead, and get the month and year of the month prior to it, correct?

=IF(AND(YEAR(TODAY()-DAY(TODAY()))=YEAR(G1),MONTH(TODAY()-DAY(TODAY()))=MONTH(G1)),1,2)

 

[cbcrocker]

I think that will work just fine, thanks Rick!

 

[dtolman]

So what if Column B has dates and I need Column A to say what month it is? So column A will say "January" if column B is 1/1/2016-1/31/2016 but "March" if 3/1/2016-3/31/2016

 

[Pattersonsqld]

So instead of having a date in cell G1, you want to use "today's date" instead, and get the month and year of the month prior to it, correct?

=IF(AND(YEAR(TODAY()-DAY(TODAY()))=YEAR(G1),MONTH(TODAY()-DAY(TODAY()))=MONTH(G1)),1,2)

I have an employee spreadsheet (from February) with all Start Dates (D2) & Termination Dates (I2).
This report is run a few weeks after the end of the month being reported on to ensure new employee information has come through from all branches.
I'm trying to return something to signify that both:
the start date falls prior or during the previous month,
and that the termination date is not in the current month (i.e. after the end of the month being reported on).

(This file is one month older than I would normally be working on).

Example:

Surname	First Name	Code	Date Hired	Location	Default Cost Account	Employment Type	Terminated	Termination Date
			5-02-2018					1-07-2014
			14-03-2011					1-07-2014
			2-09-2013					1-07-2014

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Micwhsct]

Could someone please advise what formula is required to determine if a date (dd-mmm-yyyy format) falls within a non-calendar year (April to March). For example check if the date 23 Aug'18 falls within the year 1st Apr'18 to the 31st Mar'19. I need to do this for 3 years in total, maybe there is an easy way to construct the formula to check all years at once instead of having to do it on separate tabs for each year? I believe I can do the rest of the formula, but the important part is tripping me up. Over 500 dates to check which year they relate to.
Thanks in advance.

 

[Gerald Higgins]

To check whether a single date falls between two dates, something like this should work

=and(A1< =B1,A1>=C1)

where
A1 contains the date to be analysed
B1 contains the end date of the year (or period)
C1 contains the start date of the year (or period)

Returns TRUE if the date is within the year in question.
This example also returns TRUE if the date is the same as either the year end or year start dates - if you don't want that, remove the = symbols.