LinkBack URL
About LinkBacks
I have an excel schedule that I use to display employees' shifts. The ".5" indicates half hour increments. For some reason it is not calculating correctly. I calculate just fine without the ".5" for...
I have an excel schedule that I use to display employees' shifts. The ".5" indicates half hour increments. For some reason it is not calculating correctly. I calculate just fine without the ".5" for...
1) Put actual time in your schedule, not decimal values. Making employees translate your schedule into real times is unfriendly. In the attached, I have reformatted the Management shifts into normal TIME values.
2) The formula given in W9 will accurately calculate the time between those shifts even if the shifts go past midnight.
=(IFERROR((D9-B9)+(B9>D9),0)+
IFERROR((G9-E9)+(E9>G9),0)+
IFERROR((J9-H9)+(H9>J9),0)+
IFERROR((M9-K9)+(K9>M9),0)+
IFERROR((P9-N9)+(N9>P9),0)+
IFERROR((S9-Q9)+(Q9>S9),0)+
IFERROR((V9-T9)+(T9>V9),0))*24
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks for your help! Now, I've created more problems for myself. Great note about the real time. How would I calculate the time per day vertically, which would calculate in B36? Also, what would be the best way to not calculate the time for the management team, since they are all salaried? Their time is enter just to show who is in charge of shift.
This will only work once you get all the values converted back to timevalues. This is an ARRAY formula:
B36: =SUM((IFERROR(D9:D35-B9:B35,0))+(IFERROR(B9:B15>D9:D35,0))*24)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
I'm not sure what you're asking for in relation to the management. You will apply this technique all the way down your schedule and get an accurate reflection of planned hours. Payroll is not a factor in this tool.
Oops, correction on that array formula:
=SUM((IFERROR(D9:D35-B9:B35,0))+(IFERROR(B9:B35>D9:D35,0)))*24
Thank you! Last issue, how would I conditional format a shift (B9, C9, D9) if I place a 1, 2, or 3 in C9, based on what shift.
In other words, I would like cells B9, C9, and D9 to change to a certain color if I place a 1, 2 or 3 in C9 indicating what shift the time corresponds to?
Like so...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks