How do I make Excel skip weekends? Here's what I mean:
I have a column in Excel with dates such as:
Oct 10
Oct 11
Oct 12
Oct 13
Oct 12 is a Saturday and I'd like Excel to automatically turn all Saturdays and Sundays into the following Monday. So here, I'd like Excel to automatically turn Oct 12 (which is a Sunday) into Oct 14 (which is the following Monday).
How can I do this?
Thanks
Hi,
This can be done by making use of the WEEKDAY function that is built into Excel. A Saturday will return a weekday of 7, and a Sunday will return a weekday of 1.
Take a look at my attachment. The formula is in column B. Cell B2 has a different formula to get the list started, but cells B3 and down all have a consistent formula.
Hope this helps
If your data is really date, and formatted as mmm-dd..
you can simply select the top cell..
in the right bottom cornet, Fill handle series, a black point.
select it with right click.. and drag downward.. without leaving the mouse button..
after leaving right click.. select "Fill Workdays"
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
Hi again,
I have just uploaded a version of my workbook that is compatible with your version of Excel.
Thanks ajryan88.
I need Saturdays and Sundays to turn into the following Monday.
In your workbook, Oct 12, which is a Sat, correctly turned into Oct 14, which is the following Mon.
However, Oct 13 turned into Oct 15, which is the following Tues.
Also, Oct 14, which is a Monday, should have remained Oct 14.
I only want Excel to automatically turn all Saturdays & Sundays into the following Monday. All other days need to remain the same.
Is this possible?
Thanks a million!
so you want mon tue wed thur fri mon mon mon tue wed thur fri mon mon mon
dates starting in a1
then in b1
=IF(WEEKDAY(A1)=1,A1+1,IF(WEEKDAY(A1)=7,A1+2,A1))
or
=A1+LOOKUP(WEEKDAY(A1,2),{0,6,7},{0,2,1})
Last edited by martindwilson; 09-15-2013 at 11:33 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
If you have Analysis ToolPak installed you can use WORKDAY function
=WORKDAY(A1-1,1)
Audere est facere
Sorry about my misunderstanding. Use this formula bobing:Hope this helpsFormula:
Please Login or Register to view this content.
Guys, thanks very much. I tried both daddylonglegs and ajryan88 solutions and they both worked.
One last thing I would like to exclude (along with the weekends) is holidays.
How can I exclude holidays while also excluding the weekends?
If I should start another thread for this, please let me know.
You guys are a life saver for an Excel neophyte like me. Thanks.
Unfortunately, I don't think there is a built-in way to avoid holidays...
Hi bobling..
I have little doubt "Workday" function is available with analysis pack in Excel 2003, but guessing YES..
try this..
In A1 give your first date manually.. and in A2 use formula as ..
Formula:Please Login or Register to view this content.
where HolidayList is named range or something like J1:j10 list of all dates of your region wise holiday dates..
what do you meen by holidays? when do you want them to move to? fri to mondays? what if xmas day and boxing day on tues and wed? so both those move to thursday
then you need
=WORKDAY(A1-1,1,$E$1:$E$10) where e1:e10 hold the holiday dates
to activate the analysis toolpak in excel 2003
Open Excel
then "Tools" menu. Click on "Add-Ins."
Select the check box next to Analysis ToolPak under "Add-Ins."
Click "OK."
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks