Excel Date ?
Posted by Michael Tsu on October 03, 2000 6:52 AM
ie. Oct 3, 2000 is tuesday, how do I calculate that or something similar ? like finding which day of the week that a date is ?
Posted by Ben on October 03, 2000 7:36 AM
It's easy if you want to return a number that corresponds with the day of the week. If that's all you want, just use the weekday function. If you want to return the day of the week, written as text, it's a little more complicated.
What I did is use the Weekday function in conjuction with VLOOKUP:
=VLOOKUP(WEEKDAY(B6,2),L20:M26,2,FALSE)
B6 contains a date. Cells L20:L26 are numbered from 1 to 7. M20:M26 contain the days of the week, Monday through Sunday. The formula looks for the weekday of B6 in cells L20:L26, and returns the value in M20:26 that corresponds to it.
If you try the formula, one thing you'll notice is that if B6 is blank, it'll return Saturday. Avoid this by using an IF function:
=IF(B6<>0,VLOOKUP(WEEKDAY(B6,2),L20:M26,2,FALSE),"")
I hope this helps.
-Ben
Posted by Neil on October 03, 2000 12:19 PM
Try using =WEEKDAY(A1) and using a custom format dddd
Posted by Michael Tsu on October 04, 2000 8:32 AM
Thanks, I found the weekday(x) and wrote it last night.
Thanks, one question.
Say given WEEKDAY(10-4-2000) = 4
How do you find every 3rd friday.
You guys are very helpful!
THanks.
Posted by Neil on October 04, 2000 12:26 PM
Posted by Neil on October 04, 2000 12:32 PM
This formula will find the number of days until the 3rd Friday after any date (B3 being the weekday)
=IF(6-B3<6,27-B3,IF(6-B3<6,B3-15,6))
Add the number to the original date
Then add 21 to find the second and another 21 to find the 3rd
There's probably an easier way as well!
