Need Help with Attendance Formula in an Excel Template

Good Morning and Happy New Year.

Getting ready for the new year I found an attendance record that I really like. The only issue is I cannot change the formula to get it correct in the template for this to record us as a 365 day business. We are a Drive Thru coffee business.

Help please.

This is the formula on January 1 which is a Saturday so it is blocked out:
=IFERROR(IF(TEXT(DATE(Calendar_Year,ROW($A1),1),"ddd")=LEFT(I$5,3),DATE(Calendar_Year,ROW($A1),1),IF(H6>=1,H6+1,"")),"")

This is the formula on January 3, which is a Monday so it is able to be chosen:
=IFERROR(IF(J6>=1,J6+1,""),"")

I did attached the template so you can see it.

Thank you all in advance.

Marianne

Thank you! I have just updated my profile with the following information - Office 365 App Enterprise version 2111 of Excel.

Looking forward to getting a solution!

Thank you John -

The Workings days formula worked like a charm.

My issue now is with the "conditions" I have tried to change them, but again it will not accept the formula. Or if I change the condition it still does not recognize Saturday or Sunday.

Is there any further direction you can give on the conditions?

I have tried changing the conditional formula. That does not seem to work for me.

All I need is to be shown how to change the "conditional" formula to include all days in the month. We are a 7 days a week business and I need to be able to choose a Saturday and/or Sunday as a sick day/vacation day etc.

I will attach a screen show of what I am looking at. I can also re-attach with the correct changes to the working days...

Attachment 761674

I cannot thank you all enough!! So grateful.

A very happy, healthy, and prosperous to you all.

Good Morning All -

I had an employee call in sick today. I went in the spreadsheet and added all the employees. I chose her name and added her out sick today. When I went to her name on the main page, it seems that it did not carry over and it does not show sick and it is not highlighted in blue? I know it worked when Ali did it and sent it back to me.. But now it is not.

I greatly appreciate any help. I am reattaching the updated sheet here.

Well I am an idiot. Again, thank you Ali. You are awesome.

Hi guys. I'm trying to change the weekend days to Friday and Saturday. Your kind help will be appreciate it.
MicrosoftTeams-image (7).pngMicrosoftTeams-image (7).png

=IFERROR(IF(TEXT(DATE(Calendar_Year,ROW($A1),1),"ddd")=LEFT(C$5,3),DATE(Calendar_Year,ROW($A1),1),""),"")

=IFERROR(IF(TEXT(DATE(Calendar_Year,ROW($A1),1),"ddd")=LEFT(D$5,3),DATE(Calendar_Year,ROW($A1),1),IF(C6>=1,C6+1,"")),"")

=IFERROR(IF(I6>=1,I6+1,""),"")

=NETWORKDAYS(DATE(Calendar_Year,1,1),EDATE(DATE(Calendar_Year,1,1),12)-1)

Hi John! Thanks for your quick response.

Forgive my ignorance. But now I see the working days came up to 365 days.
Can you please attach a screenshot to where I'm supposed to change the following? I'm not able to find it anywhere.
Select range C6:AR17

in last CF

=OR(WEEKDAY(C6,1)>=6,, COUNTIF(lstHolidays, C6)>0)

set FONT to White

JohnTopley, sorry for the late response. It worked! Thanks for your kind support.

Hello,

I have been able to modify the template significantly to meet what I need.
However, my company uses Fiscal year (May-April).
Is it possible to adjust formula to pull into the calendar view tab from 2 diff years? (May 2021 - Dec 2021, and Jan 2022 - April 2022)

Thank you so much! I've been stuck on this and appreciate any help!

Hi,

Anyone please help me update this excel to change my weekly off to Sunday alone?

Thanks,
Ramiz