How to change the format on this worksheet to 365 working days?

Question

Answer 1

Hi,

Based on your screenshot, the structure looks similar to the Microsoft-provided attendance tracker template. To change this template into 365 working day, please modify those things:

1/ Working Days

• Click the large Working Days number cell currently showing 261 and you see a formula using something like =NETWORKDAYS(DATE(Calendar_Year,1,1),EDATE(DATE(Calendar_Year,1,1),12)-1)
• Replace that formula with =DATE(Calendar_Year+1,1,1) - DATE(Calendar_Year,1,1)

2/ Update the last year working days

• Under the big Working Days there is a last year row which may also uses NETWORKDAYS formula as same as the Working Days at first
• Replace it with this formula =DATE(Calendar_Year,1,1) - DATE(Calendar_Year-1,1,1)

3/ Remove weekend gray formatting

• Select the whole calendar date area (H9:AW20 based on your screenshot)
• Go to Home tab > Conditional Formatting > Manage Rules
• Look for any rule that applies grey text formatting > Delete it. In the original Microsoft template, it's the rule with the formula starts with =OR(LEFT(...))

Please give it a try and let me know whether it works. If not, please feel free to reach out again in the comments on this post. I'm happy to assist you further.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Yes" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.