Need help understanding Date( ) ?

[JuanSanchez]

I think that BegDate means 6 weeks before the current day?

BUT I'm at a lose for EndDate,

BegDate = DateAdd("WW", -6, Date())
EndDate = DateAdd("d",-Weekday(Date() + 6,1),Date())

 

[PepperPepsi]

Hi chi1beast,

Yes the BegDate means 6 weeks before the current day,
the EndDate means last Sunday


Chiu Chan
cchan@gefmus.com

http://www.gefmus.com

 

[PepperPepsi]

Hi Jon,

if you do EndDate = DateAdd("d",-Weekday(Date() + 7,1),Date()) ,
you may get last Saturday but this is not accurate,
if you want to do last saturday, you need to change the firstdayofweek to 7
which is DateAdd("d",-Weekday(Date() + 6,7),Date()) in this case

here the Syntax of Weekday function,
Weekday(date, [firstdayofweek])

-----------------------------------
firstdayofweek | means
-----------------------------------
1 | Sunday (default)
2 | Monday
3 | Tuesday
4 | Wednesday
5 | Thursday
6 | Friday
7 | Saturday
-----------------------------------

notice that weekday(8) is the same as weekday(1) ** think of the mod 7 =)
let me go over how it work, for example today is
Nov29 Wednesday => 4 (according to above table), 4+6=10, Weekend(10) = 3
now by calling DateAdd function this will subtrack 3 days from current day,
where is Sunday 26th.

Let take a look at other example, if today date is Nov26 (Sun) => 1
Weekday(1+6) = weekday(7) = 7, now subtrack 7 days from Nov26 which will
give us Nov19 (Last Sunday), this is also the reason why we don't add (+7) because
(1+7) will give us 8 and weekday(8) = 1, so if the current date is Sunday we need to
force weekday function return 7 to do that we need to add 6 (1+6=7)

so.. if Sunday the weekday function will always return 7
and if not the weekday function will always return the number of days diff from last sunday

hope this helps

Chiu


Chiu Chan
cchan@gefmus.com

http://www.gefmus.com