LUIS FREUND
asked on
Calculating status on months
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you !!!!
You're welcome Luis!
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.
The suggested formula, which agrees 100% with the Asker's request, returns PAST-DUE.
@byundt
I also had the same doubts but it seems the proposed solution worked for Luis.
I also had the same doubts but it seems the proposed solution worked for Luis.
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
as shown in E2
EE-MONTH-STATUS-DWJ.xlsx
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
=IF(E2>EOMONTH(D2,0),"PAST
ASKER
Thank you very much!