Now() Function

[MadMaxx]

Anyone know a way to set the now function a week in advance.

When the user uses a form I have a text box setting the current date. But I want the date to be the monday of the next week.

 

[RichardJ]

Just use the DateAdd function to add x amount of time onto a date.

So....

DateAdd("d", 7, Now())

The first parameter is for the interval...
d for Days, m for months etc etc

the second parameter is the number of intervals, in this case 7 days.

And the final paramter is for the date to add to.


The only thing to remember with this is that It won't account for Bank Holidays or Weekends.

Cheers

 

[MadMaxx]

Now() + 7 also works but is there a way to find out what day of the week it is so I can add the right number of days to add for the next monday.

I was thinking in an if statement

if weekday =" Monday" then
format(now() + 7, "Short Date")
end if


cheers

 

[Rich]

Article ID: Q103138

you'll have to change the calculation slightly

 

[MadMaxx]

Works great,

cheers

 

[Pat Hartman]

It is better to use Date() than Now() unless you really want time included also. The Date() function returns ONLY the current date. The Now() function returns the current date PLUS the time of day.

 

[raskew]

Q: How do you calculate the next or previous Day of Week (DOW) from a given starting date?

A: Here are a pair of functions that will do it for you:

Function NextNDay(ByVal pDay As Date, wday As Integer) As Date
'*******************************************
'Name: NextNDay (Function)
'Purpose: Find next weekday (Sun, Mon, etc) following a specified date
'Note: Sun = 1 through Sat = 6
'Inputs:
' (1) ? NextNDay(date(), 3) Tuesday
' (2) ? NextNDay(#21-Feb-03#, 6) Saturday
'Output:
' (1) 2/18/03 'Today's date = 2/16/03
' (2) 2/28/03
'*******************************************

NextNDay = pDay - WeekDay(pDay) + wday + IIf(WeekDay(pDay) >= wday, 7, 0)

End Function


Function LastNDay(pDay As Date, wday As Integer) As Date
'*******************************************
'Name: LastNDay (Function)
'Purpose: Find date of day of week (Sun, Mon, etc) preceding a specified date
Note: Sun = 1 through Sat = 6
'Inputs:
'(1) ? LastNDay(date(), 3) 'Tuesday
'(2) ? LastNDay(#28-May-03#, 6) Saturday
'Output:
' (1) 5/27/03 'Today's date = 5/29/03
' (2) 5/23/03
'*******************************************

LastNDay = [pDay] - (WeekDay([pDay]) + IIf(WeekDay([pDay]) <= wday, 7, 0) - wday)

End Function


HTH,

Bob

 

[johnsqftwr]

LastNDay

Many thanks for this post, and it 'nearly' achieves what I want. However, I need the previous Sunday only if the current day isn't Sunday. If the current day is Sunday I simply require Date().

If I use LastNDay(Date(),1) it works for all days except Sunday. Today is Sunday the 22nd and it returns the 15th.

Any help gretaly appreciated.

John

 

[raskew]

Here's a revised rendition that should perform as you specified:

Function fLastNDay2(pDay As Date, wday As Integer) As Date
'*******************************************
'Name: fLastNDay2 (Function)
'Purpose: Find date of day of week (Sun, Mon, etc) preceding* a specified date
'Note: Sun = 1 through Sat = 7
'If weekday(pDay)=wDay returns pday.
'Inputs:
'(1) ? fLastNDay2(#7/22/07, 1) 'Sunday
'(2) ? fLastNDay2(#7/22/07#, 6) 'Friday
'Output:
' (1) 7/22/07
' (2) 7/20/07
'*******************************************

fLastNDay2 = IIf(WeekDay(pDay) = wday, pDay, _
             [pDay] - (WeekDay([pDay]) _
             + IIf(WeekDay([pDay]) <= wday, 7, 0) - wday))

End Function

Note: Please post to this forum rather than PMing a poster
about a previous response.

Bob