Solved

excel - employee availability remaining after scheduled

Posted on 2016-10-10
7
42 Views
Last Modified: 2016-10-12
first sheet ;
A1 is the earliest time an employee can start
B1 is the latest an employee can stay

second sheet:
A1 is the time an employee is schedule to start work
B1 is the time an employee is schedule to end work

third sheet:
this is where i need help.  
A1 and B1 are to be filled with formulas that reflect the remaining availability an employee has after being scheduled on "sheet 2".

i hope this is clear.  the file i have is quite massive and contains sensitive information so i don't want to post is here.

i appreciate your help!!
0
Comment
Question by:douglas carley
  • 3
  • 3
7 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Pls try

=Sheet2!A1-Sheet1!A1

Open in new window

and
=Sheet1!B1-Sheet2!B1

Open in new window

Regards
EE20161011..xlsx
0
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
Here's an example.

I wasn't sure if you wanted the third sheet to show the possible Start Time or the Amount of Time they would have, so I did both...

I also added some conditional formatting on the Actual Time sheet to check whether they had worked outside the times specified on their Scheduled sheet.
M--Personal-ee---Available-Schedule.xlsx
1
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
The third sheet shows the possible time of day they could start on the left, and then the amount of time they'd have available on the right.

Having multiple tabs open allows easy checking that it's doing what you want...

IMPORTANT NOTE - all times are entered in Excel's preferred format - eg 8am is entered as 8:
8pm is entered as 20:
Half past 11 in the morning would be 11:30
1
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:douglas carley
Comment Utility
thanks danch99!  that's pretty close to what i want.

what you did with the hours is close to what i'm looking to do.  if i have an employee available from say 09:00-18:00 and i schedule them 12:00-15:00 i would like to have something reflect that that person is now available for more hours of work 09:00-12:00 and 15:00-18:00.

i'm going to have to rework how i have my page set up.  thank you for your work!  it helped me realize a flaw in my request.  on the third sheet i'm going to need to have twice as many column i think.  on set for hours available before and one set for hours available after.  please let me know what you think :)
0
 

Author Comment

by:douglas carley
Comment Utility
i put together a rough file of what i think the final product might look like (without the formula(s) i need on the third sheet).
quick-schedule-for-EE.xlsx
0
 
LVL 23

Accepted Solution

by:
DanCh99 earned 500 total points
Comment Utility
Hi Douglas,
I think my first version of the sheet already told you how many hours were available both before AND after their actual shift - there were 2 tables, and this was on the one on the right?

However, I've gone a bit further and totalled these for you now, on the 4th tab.

The logic you basically need to follow is
(LatestFinish - EarliestStart) - (ActualFinish - ActualStart)
ie Their maximum possible hours minus their actual hours

Note, if any of them have shifts that run from 1 day to the next, ie they start before midnight, and end afterwards, this will need a huge amount of rework as a solution, as you need to deal with the maths not working
ie 4am - 10 pm = 6 hours in reality, but Excel will do 4 - 22 in this case, and come up with -18....

I had to change the formulas on the 3rd tab a bit, to insert Zero values if there was no availability, but to keep the table clear, I added a custom format to hide them.
Same format used on the 4th tab too.
M--Personal-ee---Available-Schedule.xlsx
0
 

Author Closing Comment

by:douglas carley
Comment Utility
it looks like everything i need is here :)  thank you.  i'm going to rework what goes where but i don't think i need anymore help.  it seems it was more simple than i thought it would be.  you were able to look at it with better eyes than mine.  i thought it was going to be a very complex formula.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now