weekday retunrs wrong number

[kizzie37]

I am using weekday against a date in 2010, I ahve run into date format issues previously, the weekday is returing the worng day number for the date in question

can anyone help?

 

[Joe4]

Please post the formula, as you have it written, and give an example of date that is not returning what you expect.

 

[kizzie37]

this is the date reference in cell A2 (1/4/2011) its formatted in English US,

- this is the formulas =IF(A2="","",WEEKDAY(A2)

the result I get is 3, it should be 2, in every case it is calculating a number either die of the correct one.

I know its something to do with the with the date formatting, in excel, but have no idea how to fix it.

I tired just using =IF(A2="","",WEEKDAY(A2)-1) but found dates further down then became incorrect also

 

[Joe4]

3 is actually correct. 1/4/2011 was a Tuesday, and Tuesday is the third day of the week (Sunday, Monday, Tuesday).

If you want your week to start on Monday, make use of the 2nd argument in the WEEKDAY function, i.e.

=WEEKDAY(A2,2)
will return 2

Here is write-up with the different arguments and options of the WEEKDAY functions: http://www.techonthenet.com/excel/formulas/weekday.php

 

[kizzie37]

think I need to take a lie down in a cool dark room...thanks