Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
No Sat & Sundays
When I set up a spreadsheet for 6 months, I have my dates across the top. eg (A-1)=Jan.1, (B-1)=Jan 2 etc. Is it possible to do this but not have any weekends? After setting the sheet up, I always go back to delete all of the Saturdays & Sundays. Is there a way to put in say, A-1=Jan 1st, click and drag across the sheet to June 30 without having any Saturdays and Sundays and still keep the dates in order? By that I mean if Friday is Jan 5th, then the next column would represent Monday Jan 7th. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=532307 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
No Sat & Sundays
Enter your first date in A1 - ensure this is not a Saturday or Sunday.
In B1 enter this formula: =IF(WEEKDAY(A1,2)=5,A1+3,A1+1) Format this as a date, then copy across your columns. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
No Sat & Sundays
lsmft, right click when you drag the formula, when you let go you will have
some options to pick from, pick fill weekdays -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "lsmft" wrote in message ... When I set up a spreadsheet for 6 months, I have my dates across the top. eg (A-1)=Jan.1, (B-1)=Jan 2 etc. Is it possible to do this but not have any weekends? After setting the sheet up, I always go back to delete all of the Saturdays & Sundays. Is there a way to put in say, A-1=Jan 1st, click and drag across the sheet to June 30 without having any Saturdays and Sundays and still keep the dates in order? By that I mean if Friday is Jan 5th, then the next column would represent Monday Jan 7th. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=532307 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
No Sat & Sundays
Enter in the starting date and then in the next column to the right insert
the following formula and copy to all remaining cells. The formula assumes that the first date is in Cell A1: =IF(WEEKDAY(A1+1)=7,A1+3,IF(WEEKDAY(A1+1) =1,A1+2,A1+1)) -- Kevin Backmann "lsmft" wrote: When I set up a spreadsheet for 6 months, I have my dates across the top. eg (A-1)=Jan.1, (B-1)=Jan 2 etc. Is it possible to do this but not have any weekends? After setting the sheet up, I always go back to delete all of the Saturdays & Sundays. Is there a way to put in say, A-1=Jan 1st, click and drag across the sheet to June 30 without having any Saturdays and Sundays and still keep the dates in order? By that I mean if Friday is Jan 5th, then the next column would represent Monday Jan 7th. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=532307 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
No Sat & Sundays
All 3 Cotton-pick'n suggestions worked just great. Thanks much to you all. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=532307 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
No Sat & Sundays
Your welcome
-- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "lsmft" wrote in message ... All 3 Cotton-pick'n suggestions worked just great. Thanks much to you all. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=532307 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i sum day of the week without counting sats and sundays? | Excel Worksheet Functions | |||
Calculate the number of Saturdays or Sundays between 2 dates? | Excel Discussion (Misc queries) | |||
Return Sundays date of current week | Excel Worksheet Functions | |||
Formula to get 1st,2nd,3rd,4th,5th Sundays from month end date. | Excel Discussion (Misc queries) | |||
Count Days excluding Sundays | Excel Worksheet Functions |