Everything not displaying correct 1st sunday of a month

[anmac1789]

Hello, in excel this formula works to find the 1st sunday of each month as I've tested this with dates in the middle of the month. My question is when I substitute this into everything, I get the wrong dates. See example

=DATE(YEAR(A1),MONTH(A1),DAY(A1))-DAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)))+8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1))-DAY(DATE(YEAR(A1),MONTH(A1),DAY(A1))))

image.png (17.29 KiB) Viewed 787 times

You can see in the above screenshot, I just have a basic date

image.png (50.59 KiB) Viewed 787 times

In the above screenshot, you can see that if I sub a date in the middle of the month it still gives me the 1st sunday of the month. However, when I substitute this into excel, I get something weird...

image.png (438.07 KiB) Viewed 787 times

In this screenshot, all this did was that it "copied" the date modified into column1. So what can I do to solve this ? How can I replicate this for the 2nd sunday of November ?

[void]

Excel date values: 1.0 = one day
Everything date values: 864000000000 = one day

It's one of quirks with Everything formulas.

Please try:

column1:=FORMATFILETIME(DATE(YEAR($dm:),MONTH($dm:),1)+(8-(WEEKDAY(DATE(YEAR($dm:),MONTH($dm:),1)-840000000000)+1))*840000000000)

more information here.

[void]

Everything 1.5.0.1384a changes WEEKDAY() to match the return values from Excel.

DATE() now returns an Excel date serial number.

addcolumn:column1 column1:=DATE(YEAR($dm:),MONTH($dm:),1)-1+(8-(WEEKDAY(DATE(YEAR($dm:),MONTH($dm:),1)-1)) column1format:date