Jun 03 2020 11:43 PM
I am struggling with a template that I use ( https://templates.office.com/en-us/employee-absence-schedule-tm03987167). I added some sheets that are the basis for the information displayed in the month sheets.
Is there a way I could use a formula to fill in the necessary fields? As we have over 70 workers, inserting all that data manually seems quite primitive (as I have done so far). I have tried to invent some kind of a formula myself but failed.
To illustrate the situation, I have added a compact version of that fail that I use (only necessary cells translated).
Jun 04 2020 12:15 AM
Jun 04 2020 12:20 AM
I need to achieve the similar result displayed on the first three sheets based on the data on sheets P, K, H, D.
Jun 04 2020 01:18 AM
First, I'd do tables for all types of off-days and in exactly the same format. For example, A is missed.
Second, you need actual dates, not only weekday names or days numbers. As variant, instead of
=TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa")
use
=DATE(CalendarYear,1,column()-column($B$1))
and in above perhaps for month number will work =MONTH(1&$B$4)
With that you may use something like
=IF(COUNTIFS(tblA,tblAnames,name, tblAstart, "<=" $ day, tblAend, ">=" $ day),"A",
IF(COUNTIFS(tblB,tblAnames,name, tblBstart, "<=" $ day, tblBend, ">=" $ day),"B",
....
)))
Jun 04 2020 10:12 PM
Thanks for the advice. I'll try to get the file reorganized and formulas adjusted.
Jul 21 2020 05:22 AM
I have reorganized the file but can't get the formula correct. Can you please help me?
Jul 21 2020 05:46 AM
Jul 21 2020 08:18 AM
You initial formula is
=IF(COUNTIFS(P!$C$3:$C$14,$D9,P!$E$3:$E$14,">=January!F$4",P!$F$3:$F$14,"<=January!F$4"),"P")
is to be changed on
=IF(COUNTIFS(P!$C$3:$C$14,$D9,P!$E$3:$E$14,"<="&January!F$4,P!$F$3:$F$14,">="&January!F$4),"P","")
In criteria it shall be like "="&A1, not "=A1"
Plus, you concatenate names, please be sure you compare correct column(s) / cell(s) with names.
In attached file formulas for January and Katrin are corrected.