Help

when does a week start?

Topic Labels: Dates & Timezones Formulas
320 0
cancel
Showing results for 
Search instead for 
Did you mean: 
kts
5 - Automation Enthusiast
5 - Automation Enthusiast
I have a sort of complicated question about how date formulas treat the concept of a 'week.'
 
This formula also considers the week to be Sunday thru Saturday:

DATETIME_FORMAT(Date, 'w')
 
For a record dated Sunday June 18, the value returned here is 25. (The same value as today, Wed June 21.)
 
 
However, consider this formula:
 
IF(
AND(
VALUE(DATETIME_FORMAT({Date},"w"))=
VALUE(DATETIME_FORMAT(NOW(),"w")),
YEAR({Date})=YEAR(NOW()))
,"Y", "N"
)
 
This formula reports whether a given record's date falls within the current week. So, a record dated Sunday June 18 would get a "N" if the week is Sun thru Sat.
 
But! A "Y" is returned, because this formula is considering a week as Mon thru Sun.
 
Does anybody know why this inconsistency would occur? Thank you for your help!
0 Replies 0