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

 

[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