| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| list formula for dates | Excel Worksheet Functions | |||
| VLOOKUP & Dates: Why is this Formula working? | Excel Worksheet Functions | |||
| date formula for extracting unique dates | Excel Worksheet Functions | |||
| Formula including dates | Excel Worksheet Functions | |||
| How do I format dates accessed by a formula | Excel Discussion (Misc queries) | |||
Linear Mode
