Weeknum() --> First Day of Week

[Swordfishtrombone]

Hi,

I have a cell (A1) that is contains a date and time (e.g.: 8/1/2018 8:00 AM)
I have another cell that uses the WEEKNUM() function to convert that date and time info to a number (The date 8/1/2018 converts to 31).
What I would like to do is to convert that number (31) to the first date of that week (7/29/2018).
I don't really care if the week starts on Sun or Mon...


Thanks in advance.

 

[steve the fish]

Start on sunday:

=INT(A1)-CHOOSE(WEEKDAY(A1),0,1,2,3,4,5,6)

Or monday:

=INT(A1)-CHOOSE(WEEKDAY(A1),6,0,1,2,3,4,5)

 

[Swordfishtrombone]

Thanks Steve, but can I get to the answer using only the WEEKNUM (31 in this example) without using the date in cell A1?

 

[steve the fish]

Probably. I dont know the rules for US week numbers though. Does this not give what you asked for?

 

[Joe4]

can I get to the answer using only the WEEKNUM (31 in this example) without using the date in cell A1?

Then what date do you want to use? The current date?
If so, just use TODAY()

 

[Swordfishtrombone]

I wanted to post my solution just to close this out.
Thanks for the help!

I multiplied the current WEEKNUM by 7 to get the day number for the start of the week.
Then I added that number of days to Jan 1 to get to the first day of the WEEKNUM, then converted that to a date.

Thanks for the responses!