Calculating hours

Question

I have Adult day care clients that need help from 2 of my staff members at certain times of the day.

They are considered 2 to 1 clients or 2:1 Clients. I created an excel but need help calculating 1:1 and 2:1 hours. A hand worked example would be

person 1 =8 hrs (7am-3pm)

person 2 = 8 hrs (7am-3pm)

note: these 2 people overlap in time

person 3 = 8 hrs (3pm-11pm)

person 4 = 8 hrs (3pm-11pm)

note: these 2 people overlap in time

person 5 = 8 hrs (11pm-7am)

note: there is no overlapping time with another person

total 1 on 1 time for this client = 8 hrs

total 2 on 1 time for this client = 16 hrs

*How do I have excel calculate this for me?*

as you can see I need to be able to cancel out time shared between staff. I also have to keep in mind that staff may checkin 30 minutes earlier than the scheduled time and that time would be considered 1 on 1 time because the 2nd person hasn't arrived yet. it will be the same for checkout. someone may check out later than the the other person and that would have to also be considered 1 on 1 time. This just means that actual check in check out time is slightly different from the scheduled check in check out time but, this must be considered when calculating total 1:1 and total 2:1. So only "actual" check in check out time matters.

Thanks in advance to anyone who can help with this.

mathetes · Answer

cywestbrook&nbsp;&nbsp;Since you have created a starter spreadsheet, would you be willing to share that? (Doing so would make it easier by far to create and test a solution for you.)&nbsp;In creating a message or posting on this forum, you should see something like this below the box into which you type. Just drag and drop the spreadsheet into that area that asks for it. If you cannot do this, put a copy on OneDrive or GoogleDrive and paste a link here that grants access to it,&nbsp;That said, may I ask a question or two:Is this layout one that you use for each of your clients, with some of them requiring only 1:1 services; others (like this example) requiring 2:1?&nbsp;Do you have a separate tab for each client?Do some clients require an occasional 3:1 service (or any other variation)?Are the three shifts shown constant for all clients?Do some clients require services for only one or two shifts?Finally, would you be willing to consider a different layout?The purpose of those questions, in case you're wondering: in designing spreadsheet solutions, it's my experience that one can always do a better job with a complete understanding of "the big picture." Hence I often ask more questions rather than just giving a simple answer to the "presenting question." [I'm sure you do the same with your clients.]

cywestbrook · Answer

mathetes&nbsp;&nbsp;I am trying to attach the document but it does not allow .xlsx format. I will try and link it but I'm also having trouble with that.&nbsp;&nbsp;Is this layout one that you use for each of your clients, with some of them requiring only 1:1 services; others (like this example) requiring 2:1?&nbsp;Only&nbsp; a few clients are 2:1. Not all.&nbsp; &nbsp; &nbsp; 2. Do you have a separate tab for each client?There is a separate tab for each client.&nbsp; &nbsp; &nbsp; 3. Do some clients require an occasional 3:1 service (or any other variation)?We do not have any 3:1 clients as of now.&nbsp; &nbsp; &nbsp; &nbsp;4. Are the three shifts shown constant for all clients?Some have up to 6 shifts. It could be anywhere from 1 shift to 6 shifts for 1 client.&nbsp; &nbsp; &nbsp; &nbsp;5. Do some clients require services for only one or two shifts?No&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 6. Finally, would you be willing to consider a different layout?&nbsp;Yes&nbsp;

mathetes · Answer

cywestbrook&nbsp;I am trying to attach the document but it does not allow .xlsx format.It absolutely allows xlsx files to be attached. Down here, below the text box where you write your inquiry. You do need to click on "Open full text editor" to see it, but rest assured that xlsx files ARE allowed on the Excel tech community board!&nbsp;And if you still can't, then go and put the file on OneDrive or GoogleDrive and paste a link here that grants access.&nbsp;Thanks for the answers to the other questions, but it will be essential to see the file.&nbsp;And while you're at that, since there are sometimes up to six shifts, it would be very helpful if you could include, either in that posted spreadsheet or in a text description, a complete description of how the shifts break down in terms of hours. The three shifts appear to be very consistent in terms of their start and stop times; are they? And hopefully the six shifts would be similarly consistent.&nbsp;

cywestbrook · Answer

mathetes&nbsp;It is attached! I was trying to drag it and it just wasn't working. Thanks.&nbsp;Please use week 8/25 and 9/1. The past weeks are not updated with formula changes.&nbsp;&nbsp;Jordan Bushman is an example of a 2:1 client.

mathetes · Answer

cywestbrook&nbsp;&nbsp;I'm having a hard time making sense of what I'm seeing in the case of Jordan Bushman. For one thing, I don't actually see any 2:1 staffing instances in the two weeks you mention. Am I missing something obvious? What is it? Or did you mean Jordan Boone? I do see some 2:1 shifts there.&nbsp;Please then, tell me exactly what it is you want Excel to do for you?&nbsp;&nbsp;If it's just a matter of "Do the check-in and check-out times in purple for any two consecutive "boxes" equal one another? If so, count as 2:1. Or do you want exact hours of 2:1 time as recorded under "Brittco," whatever that means, or under the purple (scheduled shift?)?&nbsp;And what is Brittco?&nbsp;&nbsp;But the more I think about this, the more I want "to get behind" this very elaborate system you have. (By "elaborate system" I mean such things as having one tab per client, separate sections for each day of the week, separate rows for each week, side by side data in each day for scheduled hours and actual hours).... this layout has all the markings of having been the way you (or your organization) tracked things when it was all done on large sheets of paper, so that what's happened is that you've taken a visual layout that, yes, made sense on paper, but fails to take advantage of Excel's abilities to handle transactional data, to sift and sort, categorize and summarize from a single table, a single table that contains all the data you have in those separate boxes on separate pages. What this looks like, in its current manifestation, is a system in which you (or your colleagues) do manually a LOT of work that Excel could be doing; you're asking for Excel to do the final step when it could be doing a lot of the heavier lifting.&nbsp;So, I'd like to know what the source is for all these entries on all the separate tabs and their separate boxes. Are you getting a report from a system that reports on check-in and check-out times? Are there other sources of "raw data"? How does that data get into these tabs and boxes? How much of that is done tab by tab, cell by cell, where you or somebody else enters the staff names and their check-in and check-out times, the scheduled times, etc., etc. &nbsp;&nbsp;Said another way, is there a source (are there several sources) of "raw data" preceding what you've shared here? If so, can we have a look at that "raw data"? &nbsp;If not, please describe how these little pieces of information get into this big workbook with all its client tabs and daily hours.&nbsp;&nbsp;

Forum Discussion

Calculating hours

Share

Resources