By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Determining Future Date

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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.

Nov 13 '05 #4

P: n/a
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.

Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

P: n/a
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
Expand|Select|Wrap|Line Numbers
  1. Function FutureDate(Optional pTarget As Integer = vbMonday) As Integer
  2. Dim dteNow   As Date
  3. Dim dteNew   As Date
  4. Dim intDay   As Integer
  5.  
  6. dteNow = Now()
  7. intDay = pTarget
  8. dteNew = Int(dteNow - WeekDay(dteNow) + intDay + _
  9. IIf(WeekDay(dteNow) >= intDay, 7, 0)) + #8:00:00 AM#
  10. Debug.Print dteNew
  11. FutureDate = DateDiff("h", dteNow, dteNew)
  12.  
  13. End Function
  14.  
HTH - Imboden
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.