Microsoft Office Forums If date is Current Month 1 or within 30 days

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-29-2019, 09:53 AM
Sje Sje is offline If date is Current Month 1 or within 30 days Windows 10 If date is Current Month 1 or within 30 days Office 2016
Novice
If date is Current Month 1 or within 30 days
 
Join Date: Sep 2019
Posts: 2
Sje is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 09-29-2019, 10:46 PM
ArviLaanemets ArviLaanemets is offline If date is Current Month 1 or within 30 days Windows 8 If date is Current Month 1 or within 30 days Office 2016
Expert
 
Join Date: May 2017
Posts: 478
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

When Date is within 30 days, In30=1, otherwise 0
Attached Files
File Type: xlsx DatePeriod.xlsx (10.0 KB, 2 views)
Reply With Quote
  #3  
Old 09-30-2019, 11:21 AM
Sje Sje is offline If date is Current Month 1 or within 30 days Windows 10 If date is Current Month 1 or within 30 days Office 2016
Novice
If date is Current Month 1 or within 30 days
 
Join Date: Sep 2019
Posts: 2
Sje is on a distinguished road
Default 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


Reply With Quote
  #4  
Old 09-30-2019, 10:40 PM
ArviLaanemets ArviLaanemets is offline If date is Current Month 1 or within 30 days Windows 8 If date is Current Month 1 or within 30 days Office 2016
Expert
 
Join Date: May 2017
Posts: 478
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate amount of days per month from a given timerange Yde1203 Excel 3 05-02-2019 08:13 PM
If date is Current Month 1 or within 30 days Configure for 30 days, not calendar month teapea21 Outlook 1 07-31-2014 09:05 AM
If date is Current Month 1 or within 30 days Formula to divide if no of days in month is 28 shilabrow Excel 5 06-26-2014 12:17 AM
If date is Current Month 1 or within 30 days Date Field to add 10 Days to Current Date Erbwon Word 6 11-12-2012 06:17 PM
If date is Current Month 1 or within 30 days Auto insert current month's name and current year Styler001 Word 4 01-25-2010 06:40 PM


All times are GMT -7. The time now is 10:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft