Date issues

[POLARBILLY]

Using a linked table which is an Excel download from a bookings app we use. As the “Time and Date” column was causing issues I created another field using the “Left” command and formatted it “Date”. Whilst my query accepts the date format, when I use” DatePart("w",[date])” and WeekdayName the result is a day out. E.g. it says the 5 Apr is weekday 4 – a Thursday, but it is actually a Wednesday. I then changed [date] to [date]-1 – result!. Sadly, it didn’t like the 0(Sunday) it returned and came up with “#Func!”.

All help appreciated

Yours Aye

Bill

 

[Gasman]

No, weekdayname does not return an integer, weekday does, and the default is Sunday as first day of the week, so 4 is correct for Wed
Look up the syntax for both functions, as weekdayname has same option.

 

[Pat Hartman]

Have you changed the starting day of the week to Monday?

 

[arnelgp]

it says the 5 Apr is weekday 4 – a Thursday

what is your Regional Date setting, d/m/yyyy?
it seems your calculated "Date" column has converted it into:

5/4/2023 or 05/04/2023

access uses (US-english date) format, so it interpret your "Date" column as:

May 4, 2023 (Thursday), and not April 5, 2023 (Wednesday).

 

[POLARBILLY]

Thanks to all of you for your responses - now sorted.