Results 1 to 9 of 9

Thread: help with excluding weekends in time sheet

  1. #1
    Seeker DALE1612's Avatar
    Join Date
    Sep 2015
    Posts
    15
    Articles
    0
    Excel Version
    2007

    help with excluding weekends in time sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Here is the formula that I am working with. =IF(COUNT(F69,I69)=2,MAX(0,MIN($C$6,I69)-MAX($C$5,F69))*24,""))

    I am running this formula and it is recording working hours for everyday of the week, I don't want to record weekend times, but I need to record a zero "0" for the weekends

    The weekend is Sat & Sun. Could this formula be adjusted to exclude weekends. My day & date is in Column "A" and this formula is in Column "K"

    Thanks..

  2. #2
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    94
    Articles
    0
    Excel Version
    2013, 2016, O365
    What data do those cell references (C5, C6, F69, I69) refer to?
    Can you post some sample data and the results you want to see...based on that data?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  3. #3
    Seeker DALE1612's Avatar
    Join Date
    Sep 2015
    Posts
    15
    Articles
    0
    Excel Version
    2007
    Hi Ron thanks for your question. The cells c5=NST normal start time which is 08:30am, c6=NFT normal finish time which is 17:30pm, F69=actual start time, I69=actual finish time. F & I could be any time of day, typically 04:00am - 10:00am. the working hours are flexiable so do not assume that there are any fixed working hours. I hope this helps you?

  4. #4
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    94
    Articles
    0
    Excel Version
    2013, 2016, O365
    Now, we need to know all of the pertinent fields.
    What's the structure of the data?
    How can we determine if a day is a weekend? Where are the dates entered? Are there dates entered? or are there just days of the week (Mon, Tue, etc)?
    Are multiple employees in the data? What differentiates one employee from another?
    Can you post some sample data, so we can see what you're working with?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  5. #5
    Seeker DALE1612's Avatar
    Join Date
    Sep 2015
    Posts
    15
    Articles
    0
    Excel Version
    2007
    Ron, I have attached a xls for you to see what I am trying to create. In column " K " only Monday through Friday hours worked and that are worked within the critera set by NST & NFT these relate to cells "C5 &C6" . Typicaly this would be between zero through nine only. All other hours are declared as OOH (out of hours worked). this would mean that any hours worked before the value in "C5" and after "C6". Now for weekend work. Any weekend work recorded should be displayed as zero (0) in column " K " then the weekend hours would be recorded in Column " L ". As you get a feel for this worksheet you will notice that there are no fixed hours. In answer to you question, this is a personal time sheet for one person only. I wanted to publish the .XLS but it looks like for some reason I do not have permission to attach a spreadsheet.


    Thanks
    Last edited by DALE1612; 2015-09-29 at 01:53 PM.

  6. #6
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    94
    Articles
    0
    Excel Version
    2013, 2016, O365
    Thanks for the additional explanation.
    Still don't know how to identify weekend hours though. There would need to be a "date" component somewhere in the data.
    Either as part of the time entry: 9/29/2015 9:57:00 AM
    Or based on another cell that indicates the date or the day of the week.

    So far, I'm working with this example:

    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    5
    NST
    8:30:00 AM
    6
    NFT
    5:30:00 PM
    7
    68
    Start_Time
    End_Time
    Normal
    69
    9:57:00 AM
    11:00:00 AM
    1.05
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  7. #7
    Seeker DALE1612's Avatar
    Join Date
    Sep 2015
    Posts
    15
    Articles
    0
    Excel Version
    2007
    Please see attachment.
    Attached Files Attached Files

  8. #8
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    94
    Articles
    0
    Excel Version
    2013, 2016, O365
    Glad you crossed the "able to post files" thresshold! That file was a huge help.
    Using that file...

    EDITED TO SUPPLY NEW FORMULAS (to correct a misunderstanding on my part)
    try this formula, copied down, for Normal Hours:
    Code:
    K11: =IF((COUNT($F11,$I11)=2)*(WEEKDAY($A11,2)<6),MAX(0,MIN($C$6,$I11)-MAX($C$5,$F11))*24,"")
    and this formula for OOH hours:
    Code:
    L11: =IF((COUNT($F11,$I11)=2),MAX(0,$I11-$F11)*24-N($K11),"")
    Is that something you can work with?
    Last edited by Ron Coderre; 2015-09-29 at 07:13 PM.
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  9. #9
    Seeker DALE1612's Avatar
    Join Date
    Sep 2015
    Posts
    15
    Articles
    0
    Excel Version
    2007
    Thank you Ron very much for you diligence and help over the last couple of days. I have tested all the permatations and both formulas are exactly what I was looking for. you really are a star from my side of the pond..

Tags for this Thread

Posting Permissions

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