×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Microsoft: Office FAQ

Date/Time Functions

How to determine Date of the week knowing Week Number by xlhelp
Posted: 20 Mar 08 (Edited 24 Mar 08)

Reverse of WEEKNUM

As 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))-6


Where 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+Var2


Another variation on the same formula is
=INT((TheDate-Var1)/7)*7+Var2


The 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 Var1

In 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 Saturday
and
     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 Saturday

Also, 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

My Archive

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close