Formula for Workdays

Question

I am currently creating a calendar, i need to input a text designated to each day. "WD1" for every first working day of the month then at the last day of the month it must be "WD-1" until "WD-8", weekends must be excluded into this. WD1 and so on until it meets the WD-8. I cannot create a dynamic formula for this to lessen the manual inputting of this, please help me.

Answer 1

This is what I would do:

• Start with a blank worksheet. (Don't worry, you can copy your pretty formatting over after, but again, that should usually be the last step.

• In cell A1 enter =DATE(2018,6,1)

• In cell B1 enter =A1+1

• Select (highlight) cells A1 to B1 and hit Ctrl+1 to go to number formats. Choose Custom from the left and then in the Type field enter: ddd mmm d. Click OK.

  You should have the dates of June 1st & 2nd, 2018.
  _{(I would normally just enter 2018-06-01 in A1 but that's for my country; depending on what country you're in, you're date format may be different - and if entered incorrectly won't be recognized as a date.)}

• Next, highlight cells B1 to AD1 and then hit Ctrl+R. You should now have dates for the whole month.

---

• Now click cell A1 and in the Name Box to the left of the formula bar, type FirstDay and hit Enter

  This gives the cell a Defined Name of FirstDay, like an Alias (aka "named range")

  

• Click cell AD1 (which should be June 30) and give it a name of `LastDay'

---

• In cell A2 enter formula: =IF(NETWORKDAYS(A1,LastDay)>8,NETWORKDAYS(firstDay,A1),NETWORKDAYS(A1,LastDay))

• Select (highlight) cells A2 to AD2 and then hit Ctrl+R to fill the formula to the right.

---

Now, June 30 should have a 0 below it. The hard part is done. The middle part should look something like this: (you'll see why when we finish.)

---

• Select cells A1 and A2 and hit Ctrl+1. Choose Custom again from the left side and this time the Type is \W\D-0. Click OK.

• Select cell A2 and hold Alt and press ODN to open the Conditional Formatting dialog. (Alternatively, you could click Home –> Conditional Formatting –> New Rule.)

• In Conditional Formatting dialog, click 'Use a Formula to determine which cells to format' dialogue box. In the Format values where this formula is true box type: =B2=A2 and then click the Format button.

• Under the Font tab click Color and choose WHITE.

  (We're making the cells with repeating numbers (weekends, etc) still have a value but be invisible.)

  _{(Click image to enlarge)}

• Click OK "three times" to close all the dialogs.

---

• Click cell A2 and hit Ctrl+C to Copy it.
• Select (highlight) cells A2 to AD2 and then hit Ctrl+Alt+V to open the Paste Values dialog.
• Choose Values and click OK.

---

Basically all that's left is formatting.

• Highlight all the cell by clicking the top left corner and make the cells equal width (like the beginning of the animation below). I always use "Wednesdays" for sizing since they're the widest. :-)

• Drag the cells into the positions you want, and then copy the formatting only from before or reformat now as you see fit.

---

After Formatting:

Ten minutes of formatting as re-positioning as required, and we have something similar to what your example looks like:

_{Click images to enlarge.}

_{Personally I like to throw in the sideways text to save vertical space, bu that's a matter of personal preference.}

Maybe my delivery could've been a bit better so I'll post my XLSM Demo file here in a couple minutes for you to download if you like, and then clean up the post a bit...

---

Download file here

Remember cell B2 should be set to the first day of the month, everything based off there. Have fun!

Answer 2

I agree with ashleedawg; start with a blank worksheet.

1. In A1 put 6/1/18 then merge A1:G1 and format the cell as mmmm yyyy. Apply left alignment and anything else your heart desires.

2. In A4 put,

   =$A$1-WEEKDAY($A$1, 3)+(COLUMN(A:A)-1)+INT((ROW()-ROW($4:$4))/2)*7
   

3. Apply a conditional formatting rule to A4 that will grey non-month days based on the following formula,

   =text(a4, "mmyy")<>text($a$1, "mmyy")
   

4. In A2 put,

   =UPPER(TEXT(A3, "dddd"))
   

4, in A3 put,

    =IF(AND(TEXT(A4, "mmyy") = TEXT($A$1, "mmyy"), NETWORKDAYS.INTL(A4, A4, 1)), NETWORKDAYS.INTL($A$1, A4, 1), TEXT(,))

5. Apply a custom number format of \W\D-0;;; to A3.
6. Fill A2:A4 right to G2:G4.
7. Copy A3:G4 to A5, A7, A9 and A11.
8. Format to your heart's content.

That's all you should need. If you require some oddball WD-x countdown as the days approach the end of the month, it's just maths.

To truly see the versatility of this type of maths driven calendar, change the date in A1 to another month and/or year.