Formula query

[james_simpson1998]

Hi people,

What I am trying to do is this, this is an example so not the exact wording:

Cell A4 contains a week number, example 40.
Entering forumla into cell A6: If cell A1 equals "A" and cell A2 equals "B", convert cell A4 into week number date, i.e week 40 = 28/09/2020.

is there a formula that can do this please?

thank you advance.

 

[Fluff]

Hi & welcome to MrExcel.
How about
=IF(AND(A1="A",A2="B"),DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,3))+A4*7,"")

 

[james_simpson1998]

Hi & welcome to MrExcel.
How about
=IF(AND(A1="A",A2="B"),DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,3))+A4*7,"")

Hi,

thanks for your help. Could I add into the formula if A4 = blank, then A6 = blank?

Thank you.

 

[Fluff]

How about
=IF(A4="","",IF(AND(A1="A",A2="B"),DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,3))+A4*7,""))

 

[james_simpson1998]

How about
=IF(A4="","",IF(AND(A1="A",A2="B"),DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,3))+A4*7,""))

Brilliant, thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[james_simpson1998]

Now what if I wanted to take it a step further...

what if i had a separate table on another sheet which told me, for example transit times in weeks. Lets use for example 4 weeks total transit time.

And what I would like to do is to insert a week number into cell M92 for example, and total transit time for this example is 4 weeks which is what is on the table on the other sheet. So, M92 = 35 (for example), i would like P92 to pull through the date of week 35, O92 to pull through P92-4 total weeks from the other table? Does this make sense?

 

[Fluff]

As this is now a totally different question, you will need to start a new thread.