Choosing the next date based on current date.

[Drrellik]

Hello all,

I have looked through the forums and done several searches, I found this link and I like the VBA approach to counting down to a target date: Countdown to an event?

I am creating a reminder to purchase my lottery tickets before the target date.

So opening the workbook I can use this VBA counter what I am trying to figure out is what approach to take when determining the next lottery draw

Dates are specific days of the week i.e Tuesday (3) and Friday (6) if I use =(WEEKDAY(TODAY(),1)) I can return the day of the week numeric value and format the cells as needed, what I am looking for are your thoughts on how to use simple formulas to accomplish this.

just drawing a blank and I always hate time formulas.

Cell 1 =today()
Cell 2 =next draw date (target date) either Tue or Fri
Cell 3 time remaining which I can use the VBA for the count down.

 

[FatBoyClam]

What behaviour do you want to see on a Tuesday and Friday - should it show the current date as the target or the next one (i.e. on a Tuesday show the following friday, on a Friday, the following Tuesday)

Also, do you want to consider the time element, and using "NOW()" rather than "Today()"?

Cell1 = E4 in the following examples, adjust as required.

=IF(WEEKDAY(E4,15)<5,E4-WEEKDAY(E4,15)+5,E4-WEEKDAY(E4,15)+8)
Friday to Monday this will return the following Tuesday. Tuesday to Thursday the following Friday.

=IF(WEEKDAY(E4,16)<=4,E4-WEEKDAY(E4,16)+4,E4-WEEKDAY(E4,16)+7)
Saturday to Monday this will return the following Tuesday. Wednesday & Thursday will return the following Friday. Tuesday and Friday will return the current date.

 

[Tetra201]

Here are two shorter formulas that return the same results:

=WORKDAY.INTL(E4,1,"1011011")

=WORKDAY.INTL(E4-1,1,"1011011")

 

[Drrellik]

FatBoyClam, & Tetra201 both in cell formulas work and are easy enough to follow. I was leaning towards an IFS statement and trying to evaluate <=3 >=6 and dealing with more than 24 hours was frustrating me so I am thankful for the help.