help with excluding weekends in time sheet

DALE1612

New member
Joined
Sep 22, 2015
Messages
18
Reaction score
0
Points
0
Excel Version(s)
2007
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..
 
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?
 
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?
 
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, 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:
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​
 
Please see attachment.
 

Attachments

  • Test platform V2.1.xlsx
    12.1 KB · Views: 24
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:
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..
 
Back
Top