Date Function in Access

[dhen21dx]

Hi Guys,

I need help on calculation of Time in Access. I have a Received Date and Closed Date, i do need to check if the time of Received date is =< 8:00 AM till 4:00 PM, if it is then i need to check if the time of Closing Date is at 5:00 PM (means same day closing) remarks will be Pass, Failed for those closing time 5:01 onwards. For the second validation, if received date time is 4:01 PM onwards, then 24 hours apply and remaks as Pass, if more than 24 hours mark as Failed.

Thanks

 

[xenou]

Is this still an issue?
If so, are your dates "complete" dates, with date/time values combined. E.g. "1/31/2016 10:15 AM" and so on? Best case, provide some examples of your data and a few passes and fails.

 

[dhen21dx]

Hi Xenou,

Here is the example with few pass and fails, hopefully you can help me with this one.
Thanks

Request Queue	Received Date	Fixed Date	Result	Remarks *exclude Sat and Sun
Req1	6/27/16 3:21 PM	6/27/16 3:36 PM	Pass	Received Between <=8:00 AM to 4:00 PM and closed =<5:00 PM
Req2	6/30/16 12:52 PM	6/30/16 3:23 PM	Pass	Received Between <=8:00 AM to 4:00 PM and closed =<5:00 PM
Req3	6/30/16 6:58 PM	6/30/16 7:01 PM	Pass	Received Between <=8:00 AM to 4:00 PM and closed =<5:00 PM
Req4	6/27/16 12:01 PM	6/27/16 12:16 PM	Pass	Received Between <=8:00 AM to 4:00 PM and closed =<5:00 PM
Req5	6/10/16 12:55 PM	6/10/16 3:30 PM	Pass	Received Between <=8:00 AM to 4:00 PM and closed =<5:00 PM
Req6	6/1/16 1:45 PM	6/1/16 3:42 PM	Pass	Received Between <=8:00 AM to 4:00 PM and closed =<5:00 PM
Req7	6/9/16 4:53 PM	6/16/16 8:44 AM	Fail	Received After 4:00 PM and closed >24 hours
Req8	5/30/16 3:59 PM	6/6/16 11:37 PM	Fail	Received Between <=8:00 AM to 4:00 PM but closed >5:00 PM
Req9	6/14/16 3:59 PM	6/14/16 7:36 PM	Fail	Received Between <=8:00 AM to 4:00 PM but closed >5:00 PM
Req10	6/20/16 12:50 PM	6/20/16 2:06 PM	Pass	Received Between <=8:00 AM to 4:00 PM and closed =<5:00 PM
Req11	6/16/16 4:49 PM	6/17/16 3:26 PM	Pass	Received After 4:00 PM and closed within 24 hours

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[jackd]

Which field/column represents Closing date?
What does this mean--- Between <=8:00 AM to 4:00 PM --?

Please describe how Req3 passes?????

I think you should provide more details on what makes a req Pass or Fail with examples.

 

[xenou]

Please describe how Req3 passes?????

Agree that this is a little confusing. It's not received between 8 and 4 so how do the rules apply?

 

[xenou]

Well,not sure if you are with us anymore but here's a first stab at this. I can think of two problems with this right off the bat but you have to start somewhere!!

To run, you should put this function in a new public module. Then you can use it in a query like an inbuilt function:

SELECT 
    RequestQueue, 
    ReceivedDate, 
    FixedDate, 
    PassOrFail([ReceivedDate],[FixedDate]) AS Exp1
FROM Table14;

Public Function PassOrFail(Date_Recd As Date, Date_Fixed As Date) As String

Dim Time_Received As Date
Dim Date_Due As Date
Dim Weekend_Or_Holiday_Adjustment As Long
    
    Time_Received = Date_Recd - Int(Date_Recd)
    
    '-----------------------------------------------------------------------
    '//CALCULATE DATE_DUE FOR REQUESTS RECEIVED DURING NORMAL BUSINESS HOURS
    If Time_Received >= TimeValue("08:00:00") And Time_Received <= TimeValue("16:00:00") Then
        
        '//Received during normal hours so due at 5:00
        Date_Due = Int(Date_Recd) + TimeValue("17:00:00")
    
    
    '---------------------------------------------------------------------------
    '//CALCULATE DATE_DUE FOR REQUESTS RECEIVED OUTSIDE OF NORMAL BUSINESS HOURS
    Else
        
        '//Received outside of normal hours so due next day at 5:00
        If Weekday(Date_Recd) = vbFriday Then
            Weekend_Or_Holiday_Adjustment = 3
        ElseIf Weekday(Date_Recd) = vbSaturday Then
            Weekend_Or_Holiday_Adjustment = 2
        ElseIf Weekday(Date_Recd) = vbSunday Then
            Weekend_Or_Holiday_Adjustment = 1
        End If
        Date_Due = Int(Date_Recd) + TimeValue("17:00:00") + 1 + Weekend_Or_Holiday_Adjustment
    End If
    
    '-------------------------------------------
    '//RETURN PASS OR FAIL DEPENDING ON DUE DATE
    If Date_Fixed <= Date_Due Then
        PassOrFail = "Pass"
    Else
        PassOrFail = "Fail"
    End If

End Function

 

[dhen21dx]

Hi,

Fixed date represent the closing Date. I will simplify with the rules below.

Passing rule
1. Any request with received time at 8:00 AM or 2 hours earlier until 4:00 PM and the fixed time is 5:00 PM should be Pass.
2. Any request with received time 4:01 PM and Fixed time is within 24 hours should be Pass.

Failing rule
1.Any request with received time at 8:00 AM or 2 hours earlier until 4:00 PM and the fixed time is 5:01 PM onward should be Fail.
2. Any request with received time 4:01 PM and Fixed time is over 24 hours should be Fail.

Thanks.
Dennis

 

[dhen21dx]

Hi Xenou,

Thanks, i tried the code but looks like there is a little mismatch. Below data should result to a Pass. Also can it be inserted a separate received date and closing date per country. Let say for US, should be 6:00 PM - 4:00 AM due on 5 AM and same rules apply only the time that is matter.

Received Date	Fixed Date	Exp1	Remarks
6/6/16 5:50 PM	6/7/16 5:02 PM	Fail	still within 24 hours (due until 5:50 the next day)
6/16/16 5:11 PM	6/17/16 5:10 PM	Fail	still within 24 hours (due until 5:11 the next day)

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[xenou]

In order to have different times for different countries we need to know what country applies. I don't see any country information in your data.
The new rules seem to be inexact. For instance, if received time is 6:00 PM and it is completed within 24 hours it is pass. But it's still after 5:00 PM the next day. Working out these cutoffs are very tricky. Worse if it's received at 5:00 AM. What then? Do we say they have 24 hours? Or is it due at 5:00 PM? That's not covered either (or only vaguely).

 

[dhen21dx]

Hi Xenou,

Below are the rules per country and i have some Pass and Fail sample also. The code you provided is working in regions MNL and CHN, however, i got few incorrect arguments.
Rules:

	Normal Business hours	Outside Normal Business hours
	Received Date
Country	Time Start	Time End	Closing Time	Received Date	Closing Date
USA	21:00	5:00	6:00	5:01	5:00	(24 hours)
CND	21:00	5:00	6:00	5:01	5:00	(24 hours)
MNL	8:00	16:00	17:00	16:01	16:00	(24 hours)
CHN	8:00	16:00	17:00	16:01	16:00	(24 hours)
IND	11:00	20:00	21:00	20:01	20:00	(24 hours)

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sample:

AR Company	Request Queue	Received Date	Fixed Date	Result
USA	Req1	6/10/16 23:34	6/11/16 1:48	Pass
USA	Req2	6/14/16 6:19	6/15/16 5:24	Pass
USA	Req3	6/24/16 3:01	6/25/16 15:41	Fail
USA	Req4	6/8/16 7:16	6/9/16 20:50	Fail
MNL	Req5	6/7/16 15:01	6/7/16 15:13	Pass
MNL	Req6	6/27/16 19:44	6/28/16 12:50	Pass
MNL	Req7	6/9/16 15:21	6/9/16 18:35	Fail
MNL	Req8	6/10/16 17:57	6/11/16 17:59	Fail
CHN	Req9	6/30/16 15:56	6/30/16 16:03	Fail
CHN	Req10	6/20/16 16:58	6/22/16 12:12	Fail
CHN	Req11	6/6/16 9:41	6/6/16 11:06	Pass
CHN	Req12	6/6/16 17:40	6/7/16 10:43	Pass
IND	Req13	6/30/16 13:39	6/30/16 14:01	Pass
IND	Req14	6/8/16 21:20	6/8/16 22:23	Pass
IND	Req15	6/6/16 19:07	6/6/16 23:46	Fail
IND	Req16	6/10/16 19:35	6/11/16 1:09	Fail

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Incorrect arguments in the Code:

Received Date	Fixed Date	Exp1	Remarks
6/6/16 5:50 PM	6/7/16 5:02 PM	Fail	still within 24 hours (due until 5:50 the next day)
6/16/16 5:11 PM	6/17/16 5:10 PM	Fail	still within 24 hours (due until 5:11 the next day)

<tbody>
</tbody>