I'm using this formula which allows me to calculate the date in A5 based on the date i enter in A4:
=IF(WEEKDAY(A4)=7,A4+2,IF(WEEKDAY(A4)=6,A4+3,A4+1))
Then I can fill the series by clicking and dragging from A5 to the desired date and the column is filled with my dates, Mon through Fri, to whatever end date I want.
What I'd like to be able to do is put a start date in A4 and an end date in A5 and have it generate the same Mon through Fri list.
Any thoughts?
Thank You!
Excel 2010 has a function called WORKDAY() which returns the serial number for the date before or after the specified number of days.
So,
=WORKDAY(A4,1)
For 2003 users, you would need to enable the Analysis Toolpak.
2010:http://office.microsoft.com/en-us/ex...010343038.aspx
2003:http://office.microsoft.com/en-us/ex...005209339.aspx
Last edited by Whizbang; 11-09-2011 at 03:21 PM.
Pl see attached file.After entering Start date in A4 and end date in A5 if you run macro(week_day) Weekdays will be filled in Row 4.
Last edited by kvsrinivasamurthy; 11-15-2011 at 02:56 AM.
Well that's awesome!
I know nothing about VB so it's much appreciated. Without too much work I was able to reverse engineer and get it to print the dates down the column instead (Range("A4").Offset(K, 0).Value = Stdate + T)
I have a requirement to have the start and end dates printed in their own cells, so A4 and A5 would be start and end, then the actual list would print in A6 and down, that was a little more of a challenge, but setting T=0 and K=2 seemed to work.
But one thing I'm striking out on, and this may just be my macro ignorance, but do I have to run the macro, or is there a way that once those start and end dates are entered they would just populate the list?
Thank you so much!
Hey Willmeister;
Yep, you can get the macro to auto-run whenever you populate a specific cell. Here's a resource for some additional reading on Excel VBA events, and how to write macros that trigger automatically:
http://www.ozgrid.com/VBA/run-macros-change.htm
TL;DR version of it is to move the macro from a module onto the worksheet, and then alter the macro to:
Then define the target range that you'd want the macro to run when it alters.
Private Sub Worksheet_Change(ByVal Target As Range)
It's not as bad as it sounds!
Going for Guru! Click the Star to the bottom left of this post if I helped!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks