UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Check If Today Is Between 2 Dates, Office 2010    
 
   
wornout
post Apr 29 2020, 02:58 PM
Post#1



Posts: 1,360
Joined: 17-November 13
From: Orewa New Zealand


I have tried looking and I could not find what I was after with the start date and end date being in 2 different columns.
My data on worksheets "Lists"
column AD3.....................ColumnAe3.......................ColumnAF3
01/04/2019...................31/03/2020....................2019
01/04/2020....................31/03/2021....................2020
01/04/2021.....................31/03/2022....................2021

there will be more dates but the pattern is the same ColumnA will always be the first of April and the year ColumnB with always be 31st March the following year columnC will always be the year that columnA falls on.

So what I want is some VBA to see if todays date(dd/mm/yyyy) falls between any of columnA and B and return C
So the answer in cell F25 on worksheet "CustomerMgr" for today(30/04/2020) would = 2020
The reason I want VBA because sometimes I have to manually override the year.
This vba will go inside another code that runs on a cell change
This post has been edited by wornout: Apr 29 2020, 03:33 PM
Go to the top of the page
 
tina t
post Apr 29 2020, 04:13 PM
Post#2



Posts: 6,692
Joined: 11-November 10
From: SoCal, USA


QUOTE
So what I want is some VBA to see if todays date(dd/mm/yyyy) falls between any of columnA and B and return C

what if today's date does not fall between ColumnA and ColumnB?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
wornout
post Apr 29 2020, 04:42 PM
Post#3



Posts: 1,360
Joined: 17-November 13
From: Orewa New Zealand


It will as you can see the dates are continuous.
What I ended up doing was putting a helper column in AG
In my vba I inserted the formula =AND(TODAY()>=$AD3,TODAY()<=$AE3) and filled down to last row this gave me a true or false answer then I filtered on TRUE and then copied just the year over to my othersheet
CODE
Sub Years()
Dim lastyearrow As Long
lastyearrow = Worksheets("Lists").Range("AD25").End(xlUp).Row
With Worksheets("lists")
Range("AG3").Formula = "=AND(TODAY()>=$AD3,TODAY()<=$AE3)"
Range("AG3").AutoFill Destination:=Range("AG3:AG" & lastyearrow), Type:=xlFillDefault
Range("AJ3").ClearContents 'Clear any previous results
    Sheet7.Range("AD2:AG" & lastyearrow).AdvancedFilter xlFilterCopy, CriteriaRange:=Sheet7.Range("AI2:AI3"), CopyToRange:=Sheet7.Range("AJ2"), Unique:=False
    Worksheets("CustomerMgr").Range("F25").Value = Sheet7.Range("Aj3").Value 'Bring in all WO Data

End With
  

End Sub


So now I can override my date in CustomerMgr if needed to and then reset it after
This post has been edited by wornout: Apr 29 2020, 04:43 PM
Go to the top of the page
 
RJD
post Apr 30 2020, 12:59 AM
Post#4


UtterAccess VIP
Posts: 10,596
Joined: 25-October 10
From: Gulf South USA


Hi: Since your year always starts with April 1 and ends with Mar 31 of the next year (3 month offset), couldn't you just subtract three months from today's date and get the year for that date? Then you don't need the table of years nor the helper column nor any code.

=YEAR(EDATE(TODAY(),-3))

Just a thought that might work for you...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 04:54 AM