|
Formula with dates
Hi, I'm trying to get column B (dates) to be a copy of column A (same dates but 1 month behind) and need to get the formula to ignore coloured cells (weekends).. any ideas? -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 |
Formula with dates
List all weekend dates for the next 5 years in a lookup table, then test dates for "is this a weekend date, if so ignore it otherwise use it" -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=529519 |
Formula with dates
In B1, enter:
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1 ),MONTH(A1)+1,DAY(A1))) then copy down HTH -- AP "lara5555" a écrit dans le message de ... Hi, I'm trying to get column B (dates) to be a copy of column A (same dates but 1 month behind) and need to get the formula to ignore coloured cells (weekends).. any ideas? -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 |
Formula with dates
Friday is a weekend in France Ardus? I always knew you were civilized <bg
alternative =IF(WEEKDAY(A1,2)5,"",DATE(YEAR(A1),MONTH(A1)+1,D AY(A1))) Lara, What happens when the calculated date is a weekend? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... In B1, enter: =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1 ),MONTH(A1)+1,DAY(A1))) then copy down HTH -- AP "lara5555" a écrit dans le message de ... Hi, I'm trying to get column B (dates) to be a copy of column A (same dates but 1 month behind) and need to get the formula to ignore coloured cells (weekends).. any ideas? -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 |
Formula with dates
Week-ends stretch from Thursday night to Monday aftternoon!
"Bob Phillips" a écrit dans le message de ... Friday is a weekend in France Ardus? I always knew you were civilized <bg alternative =IF(WEEKDAY(A1,2)5,"",DATE(YEAR(A1),MONTH(A1)+1,D AY(A1))) Lara, What happens when the calculated date is a weekend? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... In B1, enter: =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1 ),MONTH(A1)+1,DAY(A1))) then copy down HTH -- AP "lara5555" a écrit dans le message de ... Hi, I'm trying to get column B (dates) to be a copy of column A (same dates but 1 month behind) and need to get the formula to ignore coloured cells (weekends).. any ideas? -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 |
Formula with dates
Hi Bob, Week started with a Monday and Tues to Sat have appeared as #NAME? Bob Phillips Wrote: Friday is a weekend in France Ardus? I always knew you were civilized <bg alternative =IF(WEEKDAY(A1,2)5,"",DATE(YEAR(A1),MONTH(A1)+1,D AY(A1))) Lara, What happens when the calculated date is a weekend? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... In B1, enter: =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1 ),MONTH(A1)+1,DAY(A1))) then copy down HTH -- AP "lara5555" a écrit dans le message de ... Hi, I'm trying to get column B (dates) to be a copy of column A (same dates but 1 month behind) and need to get the formula to ignore coloured cells (weekends).. any ideas? -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 |
Formula with dates
Don't you just love France (well I do)?
Bob "Ardus Petus" wrote in message ... Week-ends stretch from Thursday night to Monday aftternoon! "Bob Phillips" a écrit dans le message de ... Friday is a weekend in France Ardus? I always knew you were civilized <bg alternative =IF(WEEKDAY(A1,2)5,"",DATE(YEAR(A1),MONTH(A1)+1,D AY(A1))) Lara, What happens when the calculated date is a weekend? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... In B1, enter: =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1 ),MONTH(A1)+1,DAY(A1))) then copy down HTH -- AP "lara5555" a écrit dans le message de ... Hi, I'm trying to get column B (dates) to be a copy of column A (same dates but 1 month behind) and need to get the formula to ignore coloured cells (weekends).. any ideas? -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 |
Formula with dates
Really? What dates are you using, and are they real dates? I don't see that
behaviour in my spreadsheet. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "lara5555" wrote in message ... Hi Bob, Week started with a Monday and Tues to Sat have appeared as #NAME? Bob Phillips Wrote: Friday is a weekend in France Ardus? I always knew you were civilized <bg alternative =IF(WEEKDAY(A1,2)5,"",DATE(YEAR(A1),MONTH(A1)+1,D AY(A1))) Lara, What happens when the calculated date is a weekend? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... In B1, enter: =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1 ),MONTH(A1)+1,DAY(A1))) then copy down HTH -- AP "lara5555" a écrit dans le message de ... Hi, I'm trying to get column B (dates) to be a copy of column A (same dates but 1 month behind) and need to get the formula to ignore coloured cells (weekends).. any ideas? -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 -- lara5555 ------------------------------------------------------------------------ lara5555's Profile: http://www.excelforum.com/member.php...fo&userid=2741 View this thread: http://www.excelforum.com/showthread...hreadid=529519 |
| All times are GMT +1. The time now is 08:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com