434,998 Members | 2,825 Online
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
6 Replies

 P: n/a On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam wrote: I am trying to write a function that determines how many hours there areuntil 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 onMonday.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 wrote:I am trying to write a function that determines how many hours there areuntil 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 onMonday. 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" wrote in message news:5M****************@fe1.columbus.rr.com... Jim Allensworth wrote: On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam wrote:I am trying to write a function that determines how many hours there areuntil 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 onMonday. 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 wrote in message news:<5M****************@fe1.columbus.rr.com>... Jim Allensworth wrote: On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam wrote:I am trying to write a function that determines how many hours there areuntil 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 onMonday. 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:... HateSpam wrote in message news:<5M****************@fe1.columbus.rr.com>... Jim Allensworth wrote: On Thu, 09 Sep 2004 22:27:40 GMT, HateSpam 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 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 Nov 13 '05 #7

### This discussion thread is closed

Replies have been disabled for this discussion.