×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft: Office FAQ

## Date/Time Functions

 How to determine Date of the week knowing Week Number by xlhelp faq68-6900 Posted: 20 Mar 08 (Edited 24 Mar 08) Reverse of WEEKNUMAs far as this FAQ goes, did I come up with the answers? ôNo, I got by with a little help from my friendsö û namely SkipVought, anotherhiggins and mintjulep.(BTW, for the young ones, thatÆs Beatles û March 29, 1967)As you probably already know, Date and Time on your PC is a constant that is continually changing. That sounds like an oxymoron, but itÆs not. ItÆs a constant because for any given instance it is a hard number that cannot be changed. Since every instance of Date & Time is different from the other because time is fleeting away, it is also a continually changing constant. As such, it makes Date and Time to be a Serial Number; a series of constants describing the passage of time. So, if Date and Time are just series of numbers, the calculations should be easy. They are, if you are only counting days, or fractions of days. The reason the calcs are not easy is because the system is not decimal based. We have base of 60, 24, 7, 30, 52, etc which don't quite sit well with our decimal minds. While Excel has some great Date-Time calculations, it falls short in many areas. For example, try figuring out someoneÆs age knowing their birth date. How do you find the date for the first day of the week knowing only the week number?  Excel has a really nice function to calculate the number of the week for a particular date (WEEKNUM), but the reverse is not available. The three formulas that work well are:=INT((DATE(YEAR(NOW()),1,1)+(A2*7)-7)/7)*7+1(credit: anotherhiggins)=DATE(YEAR(NOW()),1,1)+(A2*7)-(6+WEEKDAY(DATE(YEAR(NOW()),1,1)))(credit: mintjulep)=DATE(YEAR(NOW()),1,1)+A2/52*365-WEEKDAY(DATE(YEAR(NOW()),1,1))-6Where A2 to refers to a Week Number. For a particular date, you can calculate the "Week Of" date, using two variables below to slide the starting point one way or another.=INT((TheDate+Var1)/7)*7+Var2Another variation on the same formula is =INT((TheDate-Var1)/7)*7+Var2The formula with +Var1 slides the Week forward depending on the value of Var1. The formula with ûVar1 slides the week backward depending on the value of Var1In either case,    Var1 determines the day on which the change occurs      0 - change occurs on Saturday      1 - change occurs on Sunday      2 - change occurs on Monday      3 - change occurs on Tuesday      4 - change occurs on Wednesday      5 - change occurs on Thursday      6 - change occurs on Friday      7 - change occurs on the following Saturdayand     Var2 determines the day-of-week returned      0 - Returns Saturday      1 - Returns Sunday      2 - Returns Monday      3 - Returns Tuesday      4 - Returns Wednesday      5 - Returns Thursday      6 - Returns Friday      7 - Returns the following SaturdayAlso, to get any day of the week for a specified date you can use=TheDate+(Var3-TheDate)Where Var3 determines the day-of-week returned     1 - Returns Sunday      2 - Returns Monday      3 - Returns Tuesday      4 - Returns Wednesday      5 - Returns Thursday      6 - Returns Friday      7 - Returns Saturday Back to Microsoft: Office FAQ Index Back to Microsoft: Office Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!