Microsoft Office Forums If date is Current Month 1 or within 30 days
 Register FAQ Search Today's Posts Mark Forums Read

#1
09-29-2019, 09:53 AM
 Sje Windows 10 Office 2016 Novice Join Date: Sep 2019 Posts: 2
If date is Current Month 1 or within 30 days

I have an "if" statement to enter number 1 in column B if the date is in the current month. Works great, but limiting. I still want to count dates within the current month but also count if it is within 30 days out of todays date. So current month and +30 days out make it a 1 in column A.
I am trying to esitmate capacity for the team by identifying projects that will close within 30 days by the date entered in column A. Thoughts?
#2
09-29-2019, 10:46 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 564

When Date is within 30 days, In30=1, otherwise 0
Attached Files
 DatePeriod.xlsx (10.0 KB, 2 views)
#3
09-30-2019, 11:21 AM
 Sje Windows 10 Office 2016 Novice Join Date: Sep 2019 Posts: 2
Thank You!

Thank you for a quick response and solid answer. There isone missing element though is I want to include all dates within the activemonth as 1 also. So far your example the +30 as well as all dates in September.This allows the team members some time to close out there projects as completefor the month as I also have a filter set for all projects in-progress.

=([@Date]>=TODAY())*([@Date]<TODAY()+30)

To achieve what I wanted required me to do a 3 step processthe range dates:

• Column X
1. =IF(MONTH(J2)=MONTH(TODAY()),1,0)
• Column Z
1. =COUNTIF(J2,"<"&TODAY()+30)
• Column AA
1. =IF(OR(X2=1,Z2=1),1, 0)

Any thoughts on how that could be combined as a single formula/singlecolumn

#4
09-30-2019, 10:40 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 564

Like
Code:
`=([@Date]>=TODAY())*OR([@Date]<TODAY()+30,MONTH([@Date])=MONTH(TODAY()))`

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Yde1203 Excel 3 05-02-2019 08:13 PM teapea21 Outlook 1 07-31-2014 09:05 AM shilabrow Excel 5 06-26-2014 12:17 AM Erbwon Word 6 11-12-2012 06:17 PM Styler001 Word 4 01-25-2010 06:40 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:04 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top