Understanding how a formula works

[bobgrand]

Hello all,

I am confused with how the formula below works. What part of this formula makes the date know it's 2019?

The result I am getting in B1 is Fri, 01/04/19



Cell E3 - 1ST FRI EVERY JAN
Cell F3 - =IF(E3="","",(LEFT(E3)*7&RIGHT(E3,3)&$C$2)+1-WEEKDAY((LEFT(E3)*7&RIGHT(E3,3)&$C$2)-MATCH(MID(E3,5,2),{"mo","tu","we","th","fr","sa","su"},0)))

Any help would be greatly appreciated

Thanks
Bob

 

[steve the fish]

Re: Help with understanding how a formula works

By whatever is in C2. If C2 is empty excel will assume its the current year same as if you type 1Jan in a cell the year will be current year.

 

[bobgrand]

Re: Help with understanding how a formula works

Steve,

Did not know that. Thank you so much.

 

[Dave Patton]

Re: Help with understanding how a formula works

An alternative that you can consider

Excel 2010
	E	F
3	1ST FRI EVERY JAN	Fri Jan 4, 2019
4	1-Jan-19	Fri Jan 4, 2019
5	1Jan	Fri Jan 4, 2019
6
7	N.B. default date format is	dd/mm/yy
8
5b

Cell Formulas
Range	Formula
F3	=IF(E3="","",(LEFT(E3)*7&RIGHT(E3,3)&$C$2)+1-WEEKDAY((LEFT(E3)*7&RIGHT(E3,3)&$C$2)-MATCH(MID(E3,5,2),{"mo","tu","we","th","fr","sa","su"},0)))
F4	=WORKDAY.INTL(E4-1,1,"1111011")
F5	=WORKDAY.INTL(E5-1,1,"1111011")