+ Reply to Thread
Results 1 to 7 of 7

Struggling to apply same logic in formulas to a sheet with added columns

  1. 09-08-2022, 07:45 AM #1
    mattc_uk
    mattc_uk is offline
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    53

    Struggling to apply same logic in formulas to a sheet with added columns

    I have a spreadsheet that calculates total worked hours for users that enter their 'in' and 'out' of work times in it. The sheet calculates total hours worked and also calculates and deducts their lunch break. There is also a rule whereby if they work more than six hours, their lunch break should be a minimum of 30 minutes regardless of what their actual lunch break was.

    This works fine. However, I now need to add two iterations of IN / Out times for both AM and PM. I cannot figure out how to adjust the formula's to get it to work properly.

    Can anyone assist? Attached is an example spreadsheet with the original at the top and the new format below (without the formula's in it but has the required results (in red)

    Thanks for any help

    Matt
    Attached Files Attached Files
    Last edited by mattc_uk; 09-12-2022 at 07:48 AM.
    Reply With QuoteRegister To Reply
  2. 09-08-2022, 08:15 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Struggling to apply same logic in formulas to a sheet with added columns

    Please explain/justify the three results in the pale blue shaded cells.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 09-08-2022, 10:48 AM #3
    mattc_uk
    mattc_uk is offline
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Struggling to apply same logic in formulas to a sheet with added columns

    Hi Glen

    Cell Q13 calculates the total worked hours minus the lunch calc, In this example (H13-E13)+(L13-I13)-P13
    Cell P14 calculates the total lunch times (i.e. times between any entry in an OUT column and the subsequent IN column. So in this case (H14-G14) + (I14-H14) = 01:00. (Also, If P14 is less than O14 Then make it O14)
    Cell Q14 is the total worked hours (In the case of this row (F14-E14)+(H14-G14)+(L14-I14)

    My issue is how to dynamically calculate these entries based on which cells have been utilised. (for example in row 14 the IN and OUT columns are separated by two columns in the PM section yet this is not the case in the AM section)

    I know this is probably a very tall order but wondered if something can be done dynamically so it calculates the IN OUT times giving users the flexibility to choose any IN / Out column combination.
    Reply With QuoteRegister To Reply
  4. 09-09-2022, 01:21 PM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,821

    Re: Struggling to apply same logic in formulas to a sheet with added columns

    It seems to me that the formula in Q16:Q18 yields the correct times.
    In row 13 the hours worked are from 8:30 - 12:00 and 13:00 - 16:30 which is 7 hours. The hour taken for lunch appears to be from 12:00 - 13:00 which isn't included.
    Same general principle seems to apply to row 14, worked for 9 hours not including the two half hour breaks from 8:45 - 9:15 and 12:00 - 12:30.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 09-12-2022, 04:30 AM #5
    mattc_uk
    mattc_uk is offline
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Struggling to apply same logic in formulas to a sheet with added columns

    Yes the problem is that a user could utilise the IN/OUT columns in different ways. Simple formula's cannot deal with this.
    Reply With QuoteRegister To Reply
  6. 09-12-2022, 04:38 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Struggling to apply same logic in formulas to a sheet with added columns

    by justify.... I meant CHECK that they are CORRECT.
    Reply With QuoteRegister To Reply
  7. 09-12-2022, 07:54 AM #7
    mattc_uk
    mattc_uk is offline
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Struggling to apply same logic in formulas to a sheet with added columns

    Apologies Glenn. I misread your reply.

    Q13 - My value of 6:00 is correct (08:30-12:00)+(16:30-13:00) = 07:00. minus 01:00 break = 6:00
    P14 - My value of 01:00 is correct. (One break 13:00-12:00)
    Q14 - My value of 07:00 was incorrect. I've now updated the original spreadsheet. It should have been 08:00 (Worked for 9 hours in total with two breaks 08:45-09:15 and 12:00-12:30. Breaks between PM and Evening should be ignored)

    Thanks
    Matt
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do I set up these "helper" sheet formulas to reflect new rows added from main sheet?
    By juntjoo in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-22-2021, 04:11 AM
  2. apply formulas to sheet based on sheetname from cell
    By littleangel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-12-2021, 06:34 AM
  3. Formula needed to check my vat and net totals match my grand total and to show any differ
    By Sandpiper1809 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-13-2016, 03:47 PM
  4. Struggling with using IF and IF(AND) formulas
    By jbw1975 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2014, 05:56 PM
  5. Need to add new columns to right AND execute formulas that change as new columns are added
    By Warfarin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2013, 01:50 PM
  6. Apply formulas to the end of data of varying length in columns(please see description)
    By Exorbit in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-05-2012, 12:35 PM
  7. Macros to auto-run formulas when a new sheet is added
    By azkhan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2010, 01:08 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1