Desperate for formula help 2

[techwriterAR]

I have built a database that tracks work efficiencies. I collect the product number, date, quantity required, quantity produced, number in crew, start time, end time, set up time, and goal time. I have a forumla in a query that calculates the difference between start and end times. However I need to account for lunch breaks for first and second shifts. I can do this in excel by comparing the start time and end time to fixed references (absolute values for lunch start and end-11:30am to 12:00pm, and 9:30 pm to 10:00 pm). I have searched until I am blue in the face and have not been able to figure out how to do this in Access. Any help will be greatly appreciated.

Thanks,
gjeffcoat

 

[jfdabiri]

what is the formula in excel? let's see the formula and how you're doing it in excel.

 

[techwriterAR]

In my Excel spreadsheet, I have 4 cells set up with the following times: 11:30 AM, 12:00 PM, 9:30 PM and 10:00 PM.
The formula to check for lunch1 is: =IF(f7<=$A$1,IF(G7>$b$1,30,0),0). Colums F and G are start and end times, respectively. To check for second shift lunch, I use the same formula, but substitute the cell reference for 9:30 and 10:00 =IF(G17=$G$1,IF(H7>$H$1,30,0),0). I have these formulas in separate columns (lunch1, lunch2). I could not create a single formula that checked for both lunch times. I am entering the time values by hand in both excel and access. Formatting in excel is "00:00:00 AM". Access appears to be formatted the same way. However, I just realized that I do not have an input mask specified. Don't know if this makes a difference.

gjeffcoat

 

[PHV]

In access you may use the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jfdabiri]

you can set up those cell items in the same table:
with these columns:

lunch1_beg lunch1_end lunch2_beg lunch2_end
11:30 12:00 9:30 10:00
or as numbers 11.5, 12, 9.5, 10
you can have these input as a prompt too.

 

[techwriterAR]

THank you PHV! Took me a while to get the syntax, but your solution works like a charm. Now, if I can get just a bit more help, I need to know how to write the DateDiff formula to subtract lunch time. Right now I have this: MinutesateDiff("n",[StartTime],[EndTime]). Where do I put the check for lunch time and also, since we have 2 shifts, how do I indicate that?

Thanks,
gjeffcoat

 

[PHV]

Something like this ?
MinutesateDiff("n",[StartTime],[EndTime])-IIf([StartTime]<#10:00:00# AND [EndTime]>#09:30:00#,30,0)-IIf([StartTime]<#12:00:00# AND [EndTime]>#11:30:00#,30,0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[techwriterAR]

THANKS PHV:
Your formula worked like a charm. I really appreciate your help.

Gjeffcoat

 

[techwriterAR]

Okay, here I am with my hat in my hand. I have another question. The formula PHV gave me works as long as the time does not go past 11:59 pm. When it does, I get a negative answer. What do I need to do to this formula to get the correct number of minutes that have elapsed?
--------
Minutes: DateDiff("n",[StartTime],[EndTime])-IIf([StartTime]<#11:30:00 AM# And [EndTime]>#12:00:00 PM#,30,0)-IIf([StartTime]<#9:30:00 PM# And [EndTime]>#10:00:00 PM#,30,0)
--------
Thanks in advance for any help you can give me.
gjeffcoat

 

[Hans8823]

I think you better add the following function in a module to handle this:

Public Function Shift(ByVal startTime As Date, ByVal endTime As Date) As Date
Dim lunch1_beg  As Date
Dim lunch1_end  As Date
Dim lunch2_beg  As Date
Dim lunch2_end  As Date
    lunch1_beg = #11:30:00 AM#
    lunch1_end = #12:00:00 PM#
    lunch2_beg = #9:30:00 PM#
    lunch2_end = #10:00:00 PM#
    If endTime < startTime Then endTime = DateAdd("d", 1, endTime)
    Shift = endTime - startTime
    If startTime < lunch1_beg And endTime > lunch1_end Then
        Shift = Shift - lunch1_end + lunch1_beg
    End If
    If startTime < lunch2_beg And endTime > lunch2_end Then
        Shift = Shift - lunch2_end + lunch2_beg
    End If
    ' In the unlike event that a night shift could also end after the first lunch on the next day you should also add:
    If endTime > DateAdd("d", 1, lunch1_end) Then
        Shift = Shift - lunch1_end + lunch1_beg
    End If
End Function

 

[techwriterAR]

Hans8823,

Thanks for your response! I will give this a try.

gjeffcoat

 

[techwriterAR]

Hans8823

Thank you so much for your help. I had to make a couple of changes to your code to get the result in minutes. I really appreciate your help.

FYI: These are the changes I made:
Public Function Shift......As Integer
First Shift line from Shift=endtime-starttime to Shift=DateDiff("n", StartTime,EndTime)
and, Shift=Shift -lunch1_end+lunch1_beg to Shift = Shift-30.

Again, I want to thank you. Now I can complete this database. BTW this is my first time working with a module.

gjeffcoat

 

[Hans8823]

Your welcome,

I've build lots of 'planning' tools, so I know the problems that come up...

Sometimes it's easier to build a simple function like this in a module, instead of creating un almost unreadable SQL statement.

I wasn't sure if you wanted the shift in a date format, very good that you managed to change the code in a couple of minutes to your needs...

Good luck!