Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Dates to Next Specific Weekday Automatically
Hi, I need to take a column (C) of dates, check it against a column (B)
that contains a weekday (either Tuesday or Thursday). I then need it to check if that date in column C has already occured and then modify the cell. This all I can do. This is the tricky part for me. I need it to change the date in the cell to the same 'Tuesday' or 'Thursday' of the next month. So if the current date in C3 is the date of the 2nd tuesday of August (and B3 is 'August') , and that date has come and gone, I want it to change C3 to the 2nd tuesday of the next month, September. Any help and suggestions would be awesome. Thank you. Eric elraver AT yahoo DOT com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Dates to Next Specific Weekday Automatically
Note, this is the current macro I have written. Right now, all it does
is turn all the cells from C5 to C24 to the current date if the date in the cell has already passed. What I /want/, is if the current date in the C4 cell (for example) is the first Tuesday of the month (assuming that 'B4' = "Tuesday" rather than "Thursday"), and that day has passed, it should then update the C4 cell to the next appropriate day of the following month. Example A | B | C 5 Eric | Tuesday | 8/3/2006 It should get from Column B that this is a Tuesday night student, then it should see that 8/3/2006 has already passed. It will then realize that 8/3/2006 was the first Tuesday of this month and thus make sure that it updates the C5 cell to the first Tuesday of next month. Then... well, update it. :) Sub Auto_Open() Dim dtmFormatedDate As Date For Each c In Range("C5:C24") For Each b In Range("B5:B24") dtmFormatedDate = Format(c.Value, "YYYY,MM,DD") If dtmFormatedDate < DateTime.Date Then If b.Value = "Tuesday" Then c.Value = Format(DateTime.Date, "MM") + "/" + Format(DateTime.Date, "DD") + "/" + Format(DateTime.Date, "YYYY") ElseIf b.Value = "Thursday" Then c.Value = Format(DateTime.Date, "MM") + "/" + Format(DateTime.Date, "DD") + "/" + Format(DateTime.Date, "YYYY") End If End If Next b Next c End Sub --- Thanks for any and all help! Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WeekDay count between dates | Excel Discussion (Misc queries) | |||
using weekday formulas to automatically update a date range | Excel Discussion (Misc queries) | |||
prompt for weekday and distribute the dates in columns | Excel Discussion (Misc queries) | |||
How can I calculate dates and skip a specific weekday? | Excel Discussion (Misc queries) | |||
Determining specific weekday in a range | Excel Programming |