Solved Get date based on times (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 08:36
Joined
Jun 26, 2007
Messages
851
I'm trying to write a function for military time to get the short date of a date/time based on the time of the day for the shift that was ran.

  1. If the Date/time "DelayStart" is from 6am to 11:59pm then use the "DelayStart" given
  2. If the Date/time "DelayStart" is from Midnight to 2:59am then use "DelayStart" - 1 to give previous date.
  3. If the Date/time "DelayStart" is from 3am to 5:59 am then use the "DelayStart" given.
Code:
Public Function getDate(DelayStart as variant)

Dim h as integer: h = DatePart("h", DelayStart)
Dim m as integer: m = DatePart("n", DelayStart)

'12am to 2:29pm use previous DelayStart date
If h >= 0 And h< 2 And IIf(h = 2, IIf(m <= 59, True,False), True) Then
getDate = Format(DelayStart, "Short Date") - 1

'6am to 11:59pm use DelayStart date
ElseIf h >= 6 And h< 23 And IIf(h = 23, IIf(m <= 59, True,False), True) Then
getDate = Format(DelayStart, "Short Date")

Else
getDate = Format(DelayStart, "Short Date")
End if
End Function

Im getting incorrect dates / times for my results
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
What do get with this?
Code:
DateValue(DelayStart)+TimeValue(DelayStart)<#3:00#
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:36
Joined
Aug 30, 2003
Messages
36,118
For starters, m is month not minute; n is minute. If the input is a date/time value, I'd just test against times, like:

If TimeValue(DelayStart) < #3:00:00 AM# Then
 

oxicottin

Learning by pecking away....
Local time
Today, 08:36
Joined
Jun 26, 2007
Messages
851
For starters, m is month not minute; n is minute. If the input is a date/time value, I'd just test against times, like:

If TimeValue(DelayStart) < #3:00:00 AM# Then
Typo it was a n....
 

oxicottin

Learning by pecking away....
Local time
Today, 08:36
Joined
Jun 26, 2007
Messages
851
What do get with this?
Code:
DateValue(DelayStart)+TimeValue(DelayStart)<#3:00#
I get 0

I change post to fix... Also "DelayStart" is a Date/Time
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
I get 0

I change post to fix... Also "DelayStart" is a Date/Time
What was the value in DelayStart that resulted in 0?

Unfortunately, I can't test it right now.

Sent from phone...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:36
Joined
May 7, 2009
Messages
19,169
Code:
Public Function getDate(DelayStart As Variant)
Dim t As Variant

If IsNull(DelayStart) Then Exit Function

t = TimeValue(DelayStart)
getDate = DelayStart
    If t >= #12:00:00 AM# And t <= #2:59:59 AM# Then
        getDate = DateAdd("d", -1, DateValue(DelayStart)) + t
    End If
End Function
 

oxicottin

Learning by pecking away....
Local time
Today, 08:36
Joined
Jun 26, 2007
Messages
851
Sorry for the delay I been off.... Last two posts worked thanks....
 

Users who are viewing this thread

Top Bottom