Determining Future Date

[HateSpam]

I am trying to write a function that determines how many hours there are
until a certain date/time that depends on what today's date/time is.

Basically, how many hours from now until the next time that it is 8am on
Monday.

I've found the dateadd, hour, and weekday functions, but I can't seem to
get it straight in my head how to use them in this case.

[Jim Allensworth]

On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam <Ha******@nospam.com>
wrote:

I am trying to write a function that determines how many hours there are
until a certain date/time that depends on what today's date/time is.

Basically, how many hours from now until the next time that it is 8am on
Monday.

I've found the dateadd, hour, and weekday functions, but I can't seem to
get it straight in my head how to use them in this case.

Use DateDiff. Try this in the immediate window.

?DateDiff("h",Now(),#9/13/04 8:00 AM#)
88

So, as I write this there are 88 hours until next monday 8 AM.

- Jim

[HateSpam]

Jim Allensworth wrote:

On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam <Ha******@nospam.com>
wrote:

I am trying to write a function that determines how many hours there are
until a certain date/time that depends on what today's date/time is.

Basically, how many hours from now until the next time that it is 8am on
Monday.

Use DateDiff. Try this in the immediate window.

?DateDiff("h",Now(),#9/13/04 8:00 AM#)
88

So, as I write this there are 88 hours until next monday 8 AM.

Thanks for your reply. Actually, I already got that much though.

The trick I am looking for is determining what value should be filled in
for #9/13/04# on the fly. It needs to calculate how many hours til the
next Monday at 8am depending on WHICH Monday that happens to be based on
the current day.

[Squirrel]

Hi,
Found a couple of datetime functions that might help. Try this. I tested
it in the Immediate Window.

Public Function DoMondayAtEight()
Dim hours As Integer
Dim nextMonday As Integer
Dim result As Integer

hours = Weekday(Now()) - 1
hours = hours * 24 + Hour(Now())
Debug.Print hours

nextMonday = (vbMonday - 1) * 24
nextMonday = nextMonday + 8
Debug.Print nextMonday

If hours <= nextMonday Then
result = nextMonday - hours
Else
result = ((7 * 24) - hours) + nextMonday
End If
Debug.Print result

End Function

Linda
"HateSpam" <Ha******@nospam.com> wrote in message
news:5M****************@fe1.columbus.rr.com...

Jim Allensworth wrote:

On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam <Ha******@nospam.com>
wrote:

I am trying to write a function that determines how many hours there are
until a certain date/time that depends on what today's date/time is.

Basically, how many hours from now until the next time that it is 8am on
Monday.

Use DateDiff. Try this in the immediate window.

?DateDiff("h",Now(),#9/13/04 8:00 AM#)
88

So, as I write this there are 88 hours until next monday 8 AM.

Thanks for your reply. Actually, I already got that much though.

The trick I am looking for is determining what value should be filled in
for #9/13/04# on the fly. It needs to calculate how many hours til the
next Monday at 8am depending on WHICH Monday that happens to be based on
the current day.

[HateSpam]

Squirrel wrote:

Found a couple of datetime functions that might help. Try this. I tested
it in the Immediate Window.

[code snipped]

Perfect. You rock.

[Pieter Linden]

HateSpam <Ha******@nospam.com> wrote in message news:<5M****************@fe1.columbus.rr.com>...

Jim Allensworth wrote:

On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam <Ha******@nospam.com>
wrote:

I am trying to write a function that determines how many hours there are
until a certain date/time that depends on what today's date/time is.

Basically, how many hours from now until the next time that it is 8am on
Monday.

Use DateDiff. Try this in the immediate window.

?DateDiff("h",Now(),#9/13/04 8:00 AM#)
88

So, as I write this there are 88 hours until next monday 8 AM.

Thanks for your reply. Actually, I already got that much though.

The trick I am looking for is determining what value should be filled in
for #9/13/04# on the fly. It needs to calculate how many hours til the
next Monday at 8am depending on WHICH Monday that happens to be based on
the current day.

I started on this. The "find the next monday" thing is easy. The
trick, I guess, is to check what the current date/time it is. Then if
it's Monday, you keep going. I'll post it later today.
You basically use a do loop and add a day to a temp date. And then
you check for meeting your criteria outside the loop and you're there.

HTH,
Pieter

[Imboden]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

HateSpam <Ha******@nospam.com> wrote in message news:<5M****************@fe1.columbus.rr.com>...

Jim Allensworth wrote:

On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam <Ha******@nospam.com>
wrote:
>I am trying to write a function that determines how many hours there are
>until a certain date/time that depends on what today's date/time is.
>
>Basically, how many hours from now until the next time that it is 8am on
>Monday.

Use DateDiff. Try this in the immediate window.

?DateDiff("h",Now(),#9/13/04 8:00 AM#)
88

So, as I write this there are 88 hours until next monday 8 AM.

Thanks for your reply. Actually, I already got that much though.

The trick I am looking for is determining what value should be filled in
for #9/13/04# on the fly. It needs to calculate how many hours til the
next Monday at 8am depending on WHICH Monday that happens to be based on
the current day.

I started on this. The "find the next monday" thing is easy. The
trick, I guess, is to check what the current date/time it is. Then if
it's Monday, you keep going. I'll post it later today.
You basically use a do loop and add a day to a temp date. And then
you check for meeting your criteria outside the loop and you're there.

HTH,
Pieter

Hello -

Think this might return what you're after

Function FutureDate(Optional pTarget As Integer = vbMonday) As Integer
Dim dteNow   As Date
Dim dteNew   As Date
Dim intDay   As Integer
 
dteNow = Now()
intDay = pTarget
dteNew = Int(dteNow - WeekDay(dteNow) + intDay + _
IIf(WeekDay(dteNow) >= intDay, 7, 0)) + #8:00:00 AM#
Debug.Print dteNew
FutureDate = DateDiff("h", dteNow, dteNew)
 
End Function
 

HTH - Imboden