Find date of first day of specified week number

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,311
Office Version
  1. 365
Platform
  1. Windows
Can someone please help me?

I would like to enter a week number and have a formula report the date of the xth Monday or the day if it's a stub week. The stub week days should be week 1 and then the next Monday should be week 2.

I assembled this ugly formula, but I know there must be a better way.

Cell Formulas
RangeFormula
C4:F10C4=IF(OR(WEEKDAY(DATE(C$3,1,1),2)=1,$B4=1),DATE(C$3,1,1)+7*($B4-1),(DATE(C$3,1,1)+MOD(8-WEEKDAY(DATE(C$3,1,1),2),7))+7*($B4-2))
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,052
Office Version
  1. 2019
Platform
  1. Windows
First thing that came to mind
Excel Formula:
=WORKDAY.INTL(DATE(C$3,1,1),$B4-1,"0111111")
 
Solution

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,311
Office Version
  1. 365
Platform
  1. Windows
Jeepers creepers! How embarrassing. Thank you very much.

I struggled all afternoon for nothing. I hope I didn't brain my damage.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,613
Messages
5,549,006
Members
410,888
Latest member
leap out
Top