Schedule - counting hours - Counting hours of the schedule, weekdays, Sat and Su

Question

Hello, I need help drawing up the schedule. Attached excel file with a chart pattern. I would like to: 1. In cell N9 - the occurrences of the person in the entire schedule are counted 2. In cell O2 - hours for a given person were counted from Monday to Friday (there are changes eg 8-16, 20-8) 3....

Prot · Accepted Answer

If you change the layout of the table a little, and modify the definition of the roster ("Saturday" - this beginning on Saturday and "Sunday" on Sunday

) you can use quite simple table formulas to get the effect like on a screenshot:

2017-11...png Download (71.97 kB)

Very important in this sheet is the appropriate custom "temporary" formatting individual data columns.

The whole can be analyzed in the attached file. :spoko:

grafikPr...xlsx Download (14.65 kB)Points: 2.5 for user

michalek1988 · Answer

How do you understand that?

Prot · Answer

And you can ask for an explanation of the data in your table (no headers :cunning:

) - maybe the data from Tuesday, for example :?:

- are there any doctors on two shifts at the same time on each shift? Is it a practice that marriages or siblings of doctors are on duty on the same shift?

Possibly, what does it mean: "7.5" or "19.5" - such a fancy sign of time :?:

Ematxx · Answer

I'm sorry, I'll clarify excel. This is the case, for example, on Monday, we have 6 doctors (for example, there are 6 cabinets.) Doctors can work on the following duties: Friday Zalewski 20:00 to 8:00 and next day Saturday 08:00 to 16:00).
"Number of roster" means how many times the name KOWALSKA appeared in the entire schedule.
"Hours of a weekday" - these are hours from a doctor's schedule from Monday to Friday
: Number of Saturdays "- these are hours from Saturday rostries
"Number of Sunday hours" - these are hours on Sunday rosters

7.5 is like in reality 7:30.
If Zalewski is on Friday from 8:00 p.m. to 8:00 p.m. (that is from 8:00 p.m. to 11:59 p.m. - then we count until Friday and from 12:00 p.m. until 8:00 p.m. - we count until Saturday)

Ematxx · Answer

I have clarified excel - the person's occurrences in the entire schedule are counted - that is, according to the new excel in the Q3 cell, how many times in the entire schedule Kowalska is on duty (how many times the name Kowalska appears in the schedule)

Prot · Answer

Because I presented my solution only for one week , and extending the scope for a whole month - with table formulas may cause some problems - I am sending in an extended attachment (for the whole year ) version of the file 'counting hours of the schedule'. 2017-11...png Download (100.28 kB) grafikPr...xlsx Download (24.21 kB)Points: 3 for user

Matimen · Answer

Hello,
I have been a forum user for a long time. I found an excel file regarding the schedule. I changed a bit to my needs, but now I am not counting the hours in the table. Could someone have a peek what is wrong? I can also have this sheet corrected for a fee.

greetings

Prot · Answer

You haven't noticed, buddy, that there are a lot of so-called dynamic names (that is, names of self-adapting ranges ) :cry: I suggest you either go back to the original layout or modify all names and adapt the formulas accordingly (by entering new names ). Generally speaking, these formulas are built as array formulas (sum of the products of arrays verifying the occurrence of a criterion and arrays with the number of hours) and when modifying these formulas you have to analyze (using the button calculate now if all tables are generated according to your needs. 2020-10...png Download (76.5 kB)