Searching for the day of the week

[lawmauwa]

Hi guys, I am just a beginner in excel who needs some helps.
I have an assignement to find out the date of the first Wednesday in a given year.
I realise that if I divide the serial number of a date by 7, the remainder would tell me the day of the week (0.14285714 or 1/7 is Sunday, 0.285714 is Tuesday). But how do I extract the decimal from a division in excel? I was thinking about using the IF function to compare the remainder w/ 4/7, which is the corresponding decimal of a wednesday, to solve my problem.
Another problem is that, I cannot do DATE(2006,3,2)/7 to calculate the answer. Is there anyway to let excel understand that I need to play with the serial number of the date, but not the actual date itself?

Do you have any idea, or better solution to solve this problem?
Thanks,
Leo

[lawmauwa]

Okay, I did some research, and find out that there is a WEEKDAY() function.
But I am still interested in extracting the remainder from a division.

[oldchippy]

Hi guys, I am just a beginner in excel who needs some helps.
I have an assignement to find out the date of the first Wednesday in a given year.
I realise that if I divide the serial number of a date by 7, the remainder would tell me the day of the week (0.14285714 or 1/7 is Sunday, 0.285714 is Tuesday). But how do I extract the decimal from a division in excel? I was thinking about using the IF function to compare the remainder w/ 4/7, which is the corresponding decimal of a wednesday, to solve my problem.
Another problem is that, I cannot do DATE(2006,3,2)/7 to calculate the answer. Is there anyway to let excel understand that I need to play with the serial number of the date, but not the actual date itself?

Do you have any idea, or better solution to solve this problem?
Thanks,
Leo

Hello Leo,

This formula will find the first Wednesday in any given year, including leap years

If you put your date in cell A1, then put this in cell B1

=IF(MONTH(DATE(YEAR(A1),2,29))=2,DATE(YEAR(A1),1,1)+7-(WEEKDAY(A1)-4)-7*(WEEKDAY(A1)<2)+1,DATE(YEAR(A1),1,1)+7-(WEEKDAY(A1)-4)-7*(WEEKDAY(A1)<2))

then put this in cell C1

=TEXT(B1,"dddd mmmm yyyy")

oldchippy

[daddylonglegs]

=IF(MONTH(DATE(YEAR(A1),2,29))=2,DATE(YEAR(A1),1,1)+7-(WEEKDAY(A1)-4)-7*(WEEKDAY(A1)<2)+1,DATE(YEAR(A1),1,1)+7-(WEEKDAY(A1)-4)-7*(WEEKDAY(A1)<2))

Hi, oldchippy

I'm not sure that this works. What date should be in A1, any date? If I use 01/01/2002 in A1 I get 09/01/2002. In any case you can find the first Wednesday of the year of the date in A1 with

=DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,4))

[oldchippy]

Hi, oldchippy

I'm not sure that this works. What date should be in A1, any date? If I use 01/01/2002 in A1 I get 09/01/2002. In any case you can find the first Wednesday of the year of the date in A1 with

=DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,4))

Hi daddylonglegs,

You're right, mines rubbbish, yours works fine.

oldchippy

My memory's not as sharp as it used to be. Also, my memory's not as sharp as it used to be