douglas carley
asked on
excel - employee availability remaining after scheduled
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!!
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!!
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
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
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
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
ASKER
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 :)
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 :)
ASKER
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
quick-schedule-for-EE.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Open in new window
andOpen in new window
RegardsEE20161011..xlsx