I've helped developed a spreadsheet where I input a starting date and a lot of other cells are populated based on a formula for that cell. The spreadsheet so far avoids outputting weekends and also holidays. However, I need a way where I can get certain cells to give me only certain days of the week. For example, I want a cell to give me only Tuesdays, so if a date comes during at a Monday, I need the date automatically be bumped up to a Tuesday or if the date is after Tuesday I need it to bump it to next Tuesday. Is this something excel can do, or do I need to create my own function (which I've never done)? I'd appreciate any help. Thanks!
I don't know a good answer. All I can think of would be if you have a start date that is a Tuesday - say today - then for each output date, you could take that output date and subtract the start date, then divide that number by 7. Then roundup that number, so it will always roundup to an integer, than multiple the roundup by 7 and add to your original start date. For example, today is 8-9-11, which excel stores as 40,764. So you have a formula, and it outputs 8-25-11... which is a Thursday. Excel has this as 40,780. (1) 40,780 - 40,764 = 16 (2) 16/7 = 2.286 (3) Roundup(2.286,0) = 3 (4) 3 * 7 = 21 (5) 40,764 + 21 = 40,785 40,785 happens to be Tuesday, 8-30-11. It works, but isn't very pretty, and you'd have to think about how to roundup with different start dates, or if it does land on the Tuesday and you want to roundup to the next Tuesday. maybe it will at least get your brain thinking.
You're going to have to use a function. The WEEKDAY function will do what you're looking for. =WEEKDAY(A1) returns a numeric value, corresponding to the day of the week for the value in A1. 1=Sunday, 2=Monday,...,7=Saturday. So for your example, you could use: =IF(WEEKDAY(A1)<3,A1+(3-WEEKDAY(A1)),IF(WEEKDAY(A1)>3,A1+(10-WEEKDAY(A1)),A1))
^ I was going to edit, but just did a quick google and found the weekday function. So what Scribo said. You might want to adjust his function to replace the 3 with a hard-code cell reference, so if you want to find Wednesday's instead of Tuesday, you just have to change the one cell.
I agree with JayZ750. You can create a little lookup table with the numbers 1-7, and use the Insert->Name->Define you name them Sunday - Saturday.
