Date Range with Multiple Criteria

**Daisy Duke** · 06-13-2014, 04:01 AM

Hi, can anyone help me? I have been tearing my hair out.

I'm looking for a formula to calculate the following status tags if the date falls within these ranges; >7 days (this week) , >14 days (fortnight), 30-31 days (this month), after the month end >31 or 30 days read (next month), 3 months etc.

Today's Date eg. 10/6/2014

Finish Date: 28/9/2014

A B C
Due DateCompleted status
1 11/06/2014 this week
2 23/06/2014 fortnight
3 29/06/2014 this month
4 20/07/2014 next month
5 9/06/2014 overdue
6 3/8/2014 2 months
7 11/06/2014 12/06/2014 completed

**Miroslav R.** · 06-13-2014, 04:48 AM

Hi there,
if there is a possibility to use helper columns, check the attachement:
Book2.xlsx

Hope it helps

**FDibbins** · 06-13-2014, 11:07 AM

Daisy, welcome to the forum

Try this, copied down...
=IF(B4<=$C$2,"Complete","Next "&IF(EDATE($C$2,1)>B4,B4-$C$2&" Days",IF(EDATE($C$2,MONTH(B4)-MONTH($C$2))<B4,MONTH(B4)-MONTH($C$2)+1,MONTH(B4)-MONTH($C$2))&" Months"))

It does not gives weeks, it gives days, weeks will ret really involved, but if you really want weeks, let me know