Calculating status on months

LUIS FREUND
LUIS FREUND used Ask the Experts™
on
Wanted to obtain a status based on months.

On attachment if columns E's month is greater than columns D's month then it's "Past Due".

If column E is less than column D then it's "On-Time". or if they are equal the "On-Time".

If column F is greater than both column D and E then it's "Past Due"

See attachment for logic on column G for the end results.
EE-MONTH-STATUS.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Hi Luis,

If column F is greater than both column D and E then it's "Past Due"
As per the above condition, shouldn't G2 be PAST DUE instead of ON-TIME as F2 (May) is greater than D2 (Apr) and E2 (Apr)?

Please try this and see if that works for you...

=IF(OR(MONTH(E2&1)>MONTH(D2&1),AND(MONTH(F2&1)>MONTH(D2&1),MONTH(F2&1)>MONTH(E2&1))),"PAST DUE","ON-TIME")

Open in new window

Author

Commented:
Yes sir...you are correct.

Thank you very much!

Author

Commented:
Thank you !!!!
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Luis!
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I think there is a potential problem at the end of the year. If column D is Jan 2020, but columns E and F are both Dec 2019--shouldn't that be ON-TIME?

The suggested formula, which agrees 100% with the Asker's request, returns PAST-DUE.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
@byundt
I also had the same doubts but it seems the proposed solution worked for Luis.
Top Expert 2016

Commented:
I would change the input of the D,E,F columns to be the first day of the month but change the formatting to mmm to just show the month.
as shown in E2
EE-MONTH-STATUS-DWJ.xlsx
Rob HensonFinance Analyst

Commented:
Can probably make use of the EOMONTH function to round the earliest date to the end of the month. If the comparison date is then greater than this rounded date it has to be in the next/later month.

=IF(E2>EOMONTH(D2,0),"PAST DUE","ON-TIME")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial