CODEWITHSUNDEEP
excel-formula
user2081780
user2081780

Reputation: 29

Excel date formula to find if date in a cell is of current, next or previous month

Can anyone advise how can I find if the given date in a cell belongs to the current month or previous month or next month of the year.

For Example 

 A               B (result I want to achieve...)       
 05/12/2014      Last month     
 06/12/2014      Current month    
 07/17/2014      Next month
 06/14/2012      Past date

I am sorry if you find this question is a duplicate. I'll appreciate if you could guide me to the post where this question was answered.

Upvotes: 1

Views: 2676

Answers (2)

AHH
AHH

Reputation: 1083

You can say something like:

=(YEAR(A1) = YEAR(B1)) AND (MONTH(A1)=MONTH(B1) )

This will give you a boolean value which you can use in other cells.

Upvotes: 0

gvee
gvee

Reputation: 17161

FirstOfThisMonth:

=DATE(YEAR(NOW()), MONTH(NOW()), 1)

FirstOfNextMonth:

=DATE(YEAR(NOW()), MONTH(NOW()) + 1, 1)

FirstOfLastMonth:

=DATE(YEAR(NOW()), MONTH(NOW()) - 1, 1)

FirstOfMonthAfterNext:

=DATE(YEAR(NOW()), MONTH(NOW()) + 2, 1)

Therefore:

=IF(A2 < FirstOfLastMonth,
    "Past Date",
    IF(A2 >= FirstOfMonthAfterNext,
        "Future Date",
        If(A2 >= FirstOfNextMonth,
            "Next Month",
            "Current Month"
        )
    )
)

Upvotes: 1

Related Questions