+ Reply to Thread
Results 1 to 46 of 46

Subtracting hours in a work schedule

  1. #1
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Subtracting hours in a work schedule

    Hello,
    I am working on a monthly employee work schedule that has 2 stores. All employees work at both stores and the boss wants 2 separate schedules.
    I have attached what I have started, which at the moment I am stuck.
    I would like to have the Row 5 total hours to always reflect (subtract) a 30 minute lunch. I know it would be easier to have another column, but the boss wants it this way, UGH!!
    I also need the total hours in column H to reflect the total of each day (with the subtracted lunch break). In this column I am having the figures turn red if over 40 hours and green if it under 40 hours.
    With having 2 stores with the employees working at both, I need to somehow have the hours of the employees add up from one store to the other (a running total, if you will).
    Lastly, if I have an employee not scheduled for a day or need to put other data such as, VAC, HOL or blank cell etc in a cell, how can I get this to work and not have the formula go nuts when it is not in a time format.
    Thank you for any input anybody may have!
    Attached Files Attached Files
    Last edited by heel4you; 09-13-2009 at 01:30 AM. Reason: Title was not correctly used per terms

  2. #2
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    I have been working on my worksheet to no avail. But, I will keep trying to find my answers, trying different approaches.
    I have now run into another problem with my schedule:
    In row 4 all of my times are adding up correctly (except I would like to subtract 30 minutes for lunch times daily).
    However, as you can see, in row 8, if an employee works different hours, as in row 8 column G with 2:15-9:15 it does not compute correctly. Even my formula for for 9G is not totally correctly.
    I am hoping for some help.
    I have changed the title a few times, hoping that it meets this forums guidelines. If I am not meeting the guidelines please let me know.
    Thank you for any help.
    Attached Files Attached Files

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    It's far too much work to put multiple time values into a single cell the way you have done, effectively making them text strings, then DEconstruct the text strings with array formulas in every cell. Excel is perfectly capable of dealing with time values AS time values. You need only design your sheet in a way that gives each value its own place in the sheet.

    Like so.
    Attached Files Attached Files
    Last edited by JBeaucaire; 08-30-2009 at 02:06 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon 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!)

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Subtracting hours in a work schedule

    Quote Originally Posted by heel4you
    However, as you can see, in row 8, if an employee works different hours, as in row 8 column G with 2:15-9:15 it does not compute correctly. Even my formula for for 9G is not totally correctly.
    I agree with JB that storage is key, however, to explain the above...

    Time in XL is Decimal and Dates are Integer, ie 24 hours = 1, noon = 0.5, 6am = 0.25, 6 pm = 0.75 etc... (this is why you were multiplying your answer by 24).
    The reason therefore your formula in G9 was not working correctly was that you were adding 0.5 to the end time before subtracting the start time which basically adds 12 hours

    Edit: JB, in your file I'd alter the COUNTA to a COUNT.

    Edit: Edit: I just realised the 0.5 you were adding was to account for end time being entered as say 4:00 rather than 16:00, and so the above was still cause of error but for different reason, ie you don't need to add 12 hours for all end times (only when start time hour > end time hour!)
    Last edited by DonkeyOte; 08-30-2009 at 02:31 AM.

  5. #5
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    JBeaucaire,
    You are great. I have pulled out most of my hair on this schedule. LOL. I did not think of doing it this way and besides, I am such a newbie, I probably would have never come up with this masterpiece of formulas. Thank you so much.
    If I may impose one more question?
    With having 2 stores with the same employees working at both, (we are needing to print out separate schedules for both) how would you have the hours of the employees add up from one store to the other (a running total when we are inputting hours, on both schedules) making sure they do not have more than 40 hours? Some are part-time, so they would only be allowed 30 hours per week (Nick, Lynn, Bill and Troy).
    If I am imposing too much I completely understand.
    Thanks again!!

  6. #6
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    DonkeyOte,
    Thank you for your wonderful explanation. I am learning a great deal by doing this schedule. I am having fun, but I am frustrated at the same time. There is "always other ways to skin the cat" lol.
    Thanks again!!

  7. #7
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hello all,
    I have just heard from my boss and I am going to pull some more of my hair out!
    She does not like where I put the total hours for each day (row A5). She wants them to be in a column beside the hours of each day. It messes up my whole sheet. Why did I volunteer for this project and why would someone be so picky?!?!? Well, she's the boss and it took up too much room with the extra rows when I added all the days of the month. She wants it to be in Landscape view!
    I am still working on the 2 store formulas from what I stated in my above post. So, If anyone has any formulas from your mighty wisdom, I would greatly appreciate the help. Meanwhile, I will continue to work on what is becoming a pain in my butt...lol.
    Thanks all!!

  8. #8
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hi all,
    I am still working with the schedule and I have made some headway.
    However, there are time that an employee may not be scheduled and we need to put VAC, HOL, etc. in a time cell. How can I get this to not populate in the total? Also, when an employee is sheduled like at 2:15-9:15 the totals do not populate correctly. I FIXED THIS
    Also, I am still stuck on one more problem:
    If I may impose one more question?
    With having 2 stores (2 sheets to do and print) with the same employees working at both store on any given day, how would you have the hours of the employees add up from one store to the other (a running total when we are inputting hours, on both schedules) making sure they do not have more than 40 hours? Some are part-time, so they would only be allowed 30 hours per week (Nick, Lynn, Bill and Troy). Should I have another sheet making a list of the employees names and coming up with someway to show how many hours we are scheduling them with the rules of no more than 40 and no more than 30 for part time? Or could I put it on the side of each store sheet (outside the print area) to show the hours being scheduled?
    If I am imposing too much I completely understand.
    Thanks again!!
    Last edited by heel4you; 08-30-2009 at 03:10 PM. Reason: found another problem, FIXED ONE

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Subtracting hours in a work schedule

    Maybe like this ...
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  10. #10
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    shg,
    Looks like I can incorporate all of this....brilliant!!!
    Thank you so much!
    Hopefully the boss will not crap on this version! lol

  11. #11
    Forum Contributor trucker10's Avatar
    Join Date
    07-22-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 / 2007 / 2010 prof +
    Posts
    149

    Re: Subtracting hours in a work schedule

    and what if works from PM to AM ?
    Please Login or Register  to view this content.
    something like this start at 11:30 PM until 4:30 AM ?

  12. #12
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    OK, thanks trucker10.
    Having trouble with the running total of hours Rows B18-23 to U18-23 that shg did.
    I want to put this out to the right of each week (outside the printed area).
    I just need a total for each week of the month for each person that I may choose from the drop down list.
    Also, I must keep separate worksheet for both stores. So, they need to calculate hours between the 2 stores for each week. We don't care about totals for the whole month.
    Thanks all!!

  13. #13
    Forum Contributor trucker10's Avatar
    Join Date
    07-22-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 / 2007 / 2010 prof +
    Posts
    149

    Re: Subtracting hours in a work schedule

    You can use vlookup , sheets store 15 column Y
    Last edited by trucker10; 08-30-2009 at 06:36 PM.

  14. #14
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Thanks truck10 really great.

    In the formula: =VLOOKUP(W4,timelist,20,0)
    What is the 20, 0 in reference to?
    I am trying to understand the formulas.

    Also, I am trying to get the "HOUR CHECKER" on the right side of each worksheet (from W4:Y9) to add up totals from both sheets at one time.
    ex: If "Darcell" works at store #3 everyday except for on Monday, I want the "HOUR CHECKER" to calculate the the total hours between the 2 stores. So, when I choose "Darcell" (in any of the drop-down boxes) in the "HOUR CHECKER" I can see how many hours I have "Darcell" scheduled for both stores.

    I have attached what I have so far.
    Everyone, thanks for all the help. I am hoping that this is the last help I'll need!
    Attached Files Attached Files
    Last edited by heel4you; 09-01-2009 at 12:25 PM. Reason: forgot something

  15. #15
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hello all,
    I am still working on my spreadsheet. Trying lots of things and getting pretty frustrated. But, I will keep on pushing.

    I do have some questions and If someone has some ideas, that would be great and I would really appreciate it.

    In the formula: =VLOOKUP(W4,timelist,20,0)
    What is the 20, 0 in reference to?
    I am trying to understand the formulas.

    Also, I am trying to get the "HOUR CHECKER" on the right side of each worksheet (from W4:Y9) to add up totals from both sheets at one time.
    ex: If "Darcell" works at store #3 everyday except for on Monday, I want the "HOUR CHECKER" to calculate the the total hours between the 2 stores. So, when I choose "Darcell" (in any of the drop-down boxes) in the "HOUR CHECKER" I can see how many total hours I have "Darcell" scheduled for both stores.

    I have attached what I have so far.
    Everyone, thanks for all the help.
    Attached Files Attached Files

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    Press F1 inside of Excel and read the built-in tutorial on VLOOKUP, it explains the parameters very well.

  17. #17
    Forum Contributor trucker10's Avatar
    Join Date
    07-22-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 / 2007 / 2010 prof +
    Posts
    149

    Re: Subtracting hours in a work schedule

    Quote Originally Posted by heel4you View Post
    Hello all,

    In the formula: =VLOOKUP(W4,timelist,20,0)
    What is the 20, 0 in reference to?
    I am trying to understand the formulas.

    Everyone, thanks for all the help.
    The 20 is colunm(s) ( 20 ) of colunm R , the 0 refers to " true " you can have 3 type 0 ,1 , 2 ( true / false )http://www.ozgrid.com/Excel/excel-vlookup-formula.htm

  18. #18
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hello,
    I am still working on trying to get the VLOOKUP to work between both of my sheets in my workbook.
    I am trying to get the "HOUR CHECKER" on the right side of each worksheet (from W4:Y9) to add up totals from both sheets at one time.
    ex: If "Darcell" works at store #3 everyday except for on Monday, I want the "HOUR CHECKER" to calculate the the total hours between the 2 stores (4T on sheet "store 3" and 4T on sheet "store 15". So, when I choose "Darcell" (in any of the drop-down boxes) in the "HOUR CHECKER" I can see how many TOTAL hours I have "Darcell" scheduled for both stores.
    I have tried INDIRECT along with some other things and I cannot seem to get the formulas to work for me.
    I am attaching a copy of what I have thus far, in hopes that someone can help me out.
    Thank you!!!!
    Attached Files Attached Files

  19. #19
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    It's two sheets. Just put your two VLOOKUP formulas in one cell and add them.
    In Y4:

    =VLOOKUP($W4,hours3,20,0) + VLOOKUP($W4,hours15,20,0)

  20. #20
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hello everyone,
    The schedule that some of you assisted me with was a big hit with my boss. Thank you so much.
    She asked me if I could put a function in the spreadsheet that would count the AM (day shifts) and PM (night shift) in each single week (A4:R4) for each employee. The formula needs to calculate from sheets: store3 and store15
    Here's some of the formulas that I have tried and I am not getting anywhere (Z4:13 to AC4:13 is where the total should be):
    =(SUM
    =SUMPRODUCT
    =LEN
    =VLOOKUP
    I am attaching the file.
    Thanks for any help!!
    Attached Files Attached Files

  21. #21
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    EDIT: wrong result...see below.
    Last edited by JBeaucaire; 09-10-2009 at 06:26 AM.

  22. #22
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    EDIT: Correction.

    AB2: blank
    AC2: blank

    AB4: =SUMPRODUCT(($A$4:$A$13=$Z4) * ($B$4:$R$13<0.5) * (MOD(COLUMN($B$3:$R$3)-1,3)=1)) + SUMPRODUCT(('Store 15'!$A$4:$A$13=$Z4) * ('Store 15'!$B$4:$R$13<0.5) * (MOD(COLUMN('Store 15'!$B$3:$R$3)-1,3)=1))
    (copy down)

    AC4: =SUMPRODUCT(($A$4:$A$13=$Z4) * ($B$4:$R$13>=0.5) * (MOD(COLUMN($B$3:$R$3)-1,3)=1)) + SUMPRODUCT(('Store 15'!$A$4:$A$13=$Z4) * ('Store 15'!$B$4:$R$13>=0.5) * (MOD(COLUMN('Store 15'!$B$3:$R$3)-1,3)=1))
    (copy down)

  23. #23
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    JBeaucaire,
    Thanks. I would have never figured this one out!!!!
    Formula is not calculating the correct totals for the Day (AM shift). I'm going to try to figure it out.
    Thank you again for all of your assistance.
    You don't know how much this means to me!!!!
    Laura

    JBeaucaire,
    I believe that it is counting the blank hours as Day (AM shift). Is there anyway for the formula not to count the blank cells?
    Last edited by heel4you; 09-10-2009 at 02:32 PM. Reason: I think I figured it out

  24. #24
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    Try this, it adds one more criteria to the days formula...in AB4:

    =SUMPRODUCT(($A$4:$A$60=$Z4) * ($B$4:$R$60<0.5) * ($B$4:$R$60>0) * (MOD(COLUMN($B$3:$R$3)-1,3)=1)) + SUMPRODUCT(('Store 15'!$A$4:$A$60=$Z4) * ('Store 15'!$B$4:$R$60<0.5) * ('Store 15'!$B$4:$R$60>0) * (MOD(COLUMN('Store 15'!$B$3:$R$3)-1,3)=1))

  25. #25
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hello all,
    I would just like to say....THANK YOU to everyone that help me out with this workbook.
    I have learned a great deal with your help and I could not have done this without all of you.
    Hopefully, I am done. I can't imagine anything else the boss would want!!
    Thanks again,
    Laura

  26. #26
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Just when I thought it was over....that's what I get for thinking.
    Sometimes, the Boss (yeah, the pain in my butt one...LOL) has split schedules for days of the week. I will try to explain:
    1 person may need to open the store in the AM, leave for the afternoon then, close the store in the PM.
    The hours usually run as follows:
    8:15 am - 12:00 pm then 4:00pm - 9:15 pm with no 30 minute lunch break deduction. (9 Hours total for the day)
    or
    9:00 am - 12:00 pm then 4:00 pm - 9:15 pm with no 30 minute lunch break deduction. (8.25 Hours total for the day).
    I have tried vlookup, sum and others but as usual I can't come up with anything that does not return an error.
    When we input the "hours" for a split day, maybe it can just be typed in as "split 1" that starts at 8:15 am and "split 2" starts at 9:00 am and it can somehow reference the hours from a vlookup and calculate the proper hours in rows D,G,J,M,P and S. Then, calculate the total in row T and following up with the Grand total in row X.
    In this formula, it does not need to calculate Day and Night shifts as seen in rows AB and AC since it is both.
    I am so lost with this one!!!
    Thanks for any and all help anyone can give. I am attaching the file for reference.
    Laura
    Attached Files Attached Files

  27. #27
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    To add in the hours for split shifts you've entered as SPLIT1 and SPLIT2, put this formula in D4 and copy down:
    =IF(COUNTA(B4:C4)=2,(C4-B4)*24-0.5,IF(B4="SPLIT1",9,IF(B4="SPLIT2",7.25,0)))

    UPDATE: Since you appear to have decided NOT to have one group of names collect all the hours from all the sections on both sheet, the formulas needed in AB and AC can simplify down.

    AB:
    =SUMPRODUCT(($B4:$R4<0.5) * ($B4:$R4>0) * (MOD(COLUMN($B3:$R3)-1,3)=1)) + SUMPRODUCT(('Store 15'!$B4:$R4<0.5) * ('Store 15'!$B4:$R4>0) * (MOD(COLUMN('Store 15'!$B3:$R3)-1,3)=1))

    AC:
    =SUMPRODUCT(($A$4:$A$13=$Z4) * ($B$4:$R$13>=0.5) * (MOD(COLUMN($B$3:$R$3)-1,3)=1)) + SUMPRODUCT(('Store 15'!$A$4:$A$13=$Z4) * ('Store 15'!$B$4:$R$13>=0.5) * (MOD(COLUMN('Store 15'!$B$3:$R$3)-1,3)=1))

    NOTE: The two formulas above do not require you to press CTRL-SHIFT-ENTER to confirm them. I noticed you had array-entered the original formulas and want to make sure you know that is not needed.

    AD3 - SPLIT1
    AD4:
    =COUNTIF($B4:$R4,AD$3)

    AE3 - SPLIT2
    AE4:
    =COUNTIF($B4:$R4,AE$3)
    Last edited by JBeaucaire; 09-14-2009 at 12:28 PM. Reason: Sheet removed...see below for latest version

  28. #28
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hi there,
    "SPLIT2" is coming up 7.3 hours. It should be 7.25. I can't figure out why.

    =IF(COUNTA(B5:C5)=2,(C5-B5)*24-0.5,IF(B5="SPLIT1",9,IF(B5="SPLIT2",7.25,0)))


    Laura
    Last edited by heel4you; 09-14-2009 at 11:53 AM. Reason: added comment

  29. #29
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    Sounds like the formatting of the cell needs to be adjusted. Click the Increase Decimal icon on the Formatting Toolbar.

  30. #30
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hello all,

    The "SPLIT1" and " "SPLIT2" is working great, but I also need to add the following criteria:

    =IF(COUNTA(B4:C4)=2,(C4-B4)*24-0.5,IF(B4="SPLIT1",9,IF(B4="SPLIT2",7.25,IF(B4="VAC6.5",6.5,IF(B4="VAC6.25",6.25,IF(B4="VAC6.75",6.7 5,IF(B4="VAC7.25",7.25,IF(B4="WD",6.5,IF(B4="HOLIDAY",6.5,0)))))))

    The above represents other information that we may need to type into the schedule that needs to calculate into hours. If we type in "VAC6.5" that is 6.5 hours or "VAC6.25" (6.25) or "VAC6.75" (6.75) or "VAC7.25" (7.25). For "WD" (weekday off) which is 6.5 hours and "HOLIDAY", which is 6.5.

    It might be that I am not closing the formula correctly. I have checked it and I can't figure it out, once again!

    Thank you,
    Laura
    Last edited by heel4you; 09-14-2009 at 12:03 PM. Reason: adding explaination

  31. #31
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    I suggested my original approach because it was only two values. With this larger list, you need to put in a "chart" somewhere and just refer to it.

    Here I've added the chart to the last page and then changed the formula in D4 to:

    =IF(B4="", 0, IF(COUNTA(B4:C4)=2, (C4-B4) * 24 - 0.5, IF(ISNUMBER(MATCH(B4, 'Lists for Stores'!$C:$C,)), INDEX('Lists for Stores'!$D:$D, MATCH(B4,'Lists for Stores'!$C:$C,)), 0)))


    NOTE: The reason your nested IF(IF(IF))) wasn't working was you exceeded the nesting limitations. Anytime you find yourself evaluating the SAME cell over and over with multiple IFs, there's usually a better way with a lookup table of some sort.
    Attached Files Attached Files
    Last edited by JBeaucaire; 09-14-2009 at 12:30 PM.

  32. #32
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Just beautiful formatting.
    Everything seems to be working great!!
    Hopefully, I am done.
    Thank you,
    Laura

  33. #33
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

  34. #34
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    I wish I was done.
    1 more question:
    On the formula
    =IF(B4="", 0, IF(COUNTA(B4:C4)=2, (C4-B4) * 24 - 0.5, IF(ISNUMBER(MATCH(B4, 'Lists for Stores'!$C:$C,)), INDEX('Lists for Stores'!$D:$D, MATCH(B4,'Lists for Stores'!$C:$C,)), 0)))
    I need this to calculate between both store 3 and 15.
    I have also added the formula (chart) in store 15. It calculates great for each of the 2 stores separately but, I need it to calculate (reference) both stores on the chart.
    Thanks,
    Laura

  35. #35
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    If you're talking about cell D4, that make no sense based on the changes so far. D4 clearly is the hours in B4:C4. You have the same column on sheet2 to cover the other store, and you can sum the total hours of the two sheets off to the right, where it makes sense to do so.


    If you really expect D4 to reflect both sheets (which will read very oddly since you will only be able to see one sheet, but see a total for two...very odd)...then you will have to create the same formula twice in that cell: Once as is.. then + the same formula again, this time time "Store 15" sheet references added to everything:

    =IF(B4="", 0, IF(COUNTA(B4:C4)=2, (C4-B4) * 24 - 0.5, IF(ISNUMBER(MATCH(B4, 'Lists for Stores'!$C:$C,)), INDEX('Lists for Stores'!$D:$D, MATCH(B4,'Lists for Stores'!$C:$C,)), 0))) + IF('Store 15'!B4="", 0, IF(COUNTA('Store 15'!B4:C4)=2, ('Store 15'!C4-'Store 15'!B4) * 24 - 0.5, IF(ISNUMBER(MATCH('Store 15'!B4, 'Lists for Stores'!$C:$C,)), INDEX('Lists for Stores'!$D:$D, MATCH('Store 15'!B4,'Lists for Stores'!$C:$C,)), 0)))
    Last edited by JBeaucaire; 09-16-2009 at 02:29 AM.

  36. #36
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    I believe that you have misunderstood me.
    I merely want to put the table (AE:AM) on both sheet, or leave it on store 3 page, but the totals need to reflect both stores input.
    When I showed it to her, it did not keep a running total between both store, it only totaled store 3.
    I have attached the file as it is so far.
    I am talking about the chart in AE:AM.
    I hope this is more clear.
    Thanks
    Attached Files Attached Files
    Last edited by heel4you; 09-16-2009 at 10:41 PM. Reason: misunderstanding

  37. #37
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    I believe that this is a misunderstanding.
    I want to put the table (AE:AM on sheet "store3") on both sheet, or leave it on store 3 page, but the totals need to reflect both stores input.
    When I showed it to her, it did not keep a running total between both store, it only totaled store 3.
    I have attached the file as it is so far.
    I am talking about the chart in AE:AM.
    Sorry for any confusion that I may have caused.
    Thanks
    Attached Files Attached Files

  38. #38
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    Well, you keep asking new questions that have the same answer, hehe. If you want one cell to reflect the same formula for both sheets, put both formula in one cell and add them.

    AG4:
    =COUNTIF($B4:$R4,AG$3) +COUNTIF('Store 15'!$B4:$R4,AG$3)
    ...formatted as General and then copied down and over.

  39. #39
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    Hello all,
    Well, after a major meeting with the boss, guess what, she wants a change.
    I have attached the new file.
    She would like both the stores 93 and 15) on one sheet (which I have done).
    I have the totals for (Grand Total) AR:AT completed correctly. However, in the employee "#day" and "#night" in columns AV:AY are not. I have tried a couple of different things to no avail. The total in AV:AM need to show the number of day shifts and night shifts across both stores for each employee.
    Would someone give me a hand on this?
    I would really appreciate it.
    Thanks!!!
    Attached Files Attached Files

  40. #40
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    In AX2:
    =SUMPRODUCT(--($B5:$AM5<0.5), --($B5:$AM5>0), --(MOD(COLUMN($B5:$AM5)-1,3)=1))

    In AY2:
    =SUMPRODUCT(--($B5:$AM5>0), --($B5:$AM5<1), --(MOD(COLUMN($B5:$AM5)-1,3)=1)) - AX5

  41. #41
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    jbeaucaire,
    I tried the formula and it is coming up with an error (just a dot).
    Here's what I have in the attached file.
    Thanks
    Attached Files Attached Files

  42. #42
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    That's not a dot, that's a ZERO in a REALLY hard to read font/size.

    I guess it I copied those formulas from row 5 and they don't belong in row 2 at all, my mistake and you didn't catch it either. Put the formulas as shown into row 5 then copy them up and down.

  43. #43
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    JBeaucaire,
    I tried that before I posted my last reply.
    It does not work.
    I have attached what I have.
    Thanks
    Attached Files Attached Files

  44. #44
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    Man, you got some copying issues. When you put a formula in row FOUR, make sure the references IN the formula are looking across row FOUR. You have the exact same formula in AX4 and AX5 which is looking at row FIVE, which is blank. So the answer is zero.

    The formula is working. Make sure the formulas you are "analyzing" are appropriately entered for the row.

    Copy the CELL AX5 up over AX4 and it will adjust the formula for you.

  45. #45
    Registered User
    Join Date
    08-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2002
    Posts
    25

    Re: Subtracting hours in a work schedule

    It's working now.
    I had copied right from the text in your post. I did not notice that the formula was written for row 5 since it should have targeted row 4. I made the adjustment and it worked. My bad.
    Thanks for the help.

  46. #46
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Subtracting hours in a work schedule

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and add [SOLVED] to start of the title.


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1