How to format 02/2022 as weeknum/year and calculate the difference between weeks

[CS297]

This should be so simple but I cannot find a way to do it.
I have two lots of data. One is a week number and year 02/2022, formatted as General (from a data download). One is a date which I have converted to Weeknum/Year. I want to add a formula to deduct one from the other. Can't find a way to format the 02/2022 so Excel recognizes it as weeknum/year. I then need a formula to calculate the time between these two week numbers (i.e. 02/2022 - 47/2021 = 7 (weeks)) - it doesn't seem to work as a basic subtraction when I test it. Thanks!

 

[Joe4]

One is a week number and year 02/2022, formatted as General

The only way it would show 02/2022 if the format is set to "General" is if it is, in fact, actually a text entry (if you look at the value in the formula editor, F2, you will probably see a single apostophe in front of it, i.e. '02/2022). If it was not entered as text, it would show it as a date (displayed like "Feb-22").

That being said, you should then be able to use the Date function to convert it to a valid date.
So, for a value in cell A9, to convert it to a valid date, with the first day of the month, you can use this formula:

=DATE(RIGHT(A9,4),LEFT(A9,2),1)

Then, to calculate your number of weeks, simply subtract your two dates and divide by 7 (the number of days in a week).

 

[CS297]

The only way it would show 02/2022 if the format is set to "General" is if it is, in fact, actually a text entry (if you look at the value in the formula editor, F2, you will probably see a single apostophe in front of it, i.e. '02/2022). If it was not entered as text, it would show it as a date (displayed like "Feb-22").

That being said, you should then be able to use the Date function to convert it to a valid date.
So, for a value in cell A9, to convert it to a valid date, with the first day of the month, you can use this formula:

=DATE(RIGHT(A9,4),LEFT(A9,2),1)

Then, to calculate your number of weeks, simply subtract your two dates and divide by 7 (the number of days in a week).

Hi Joe4,
Thank you for replying.
Have tried your suggestion on week 52/2021 and it returns 01/04/2025 - no idea where it gets that from! Also, I can't work with 1st day of the month. Week 52 2021 started on Monday 27th December so I need 27/12/2021. If converting 02/2022 to a date, I need it to return 10/01/2022. Hope that makes sense!
So, there is no way to format the 02/2022 so it recognizes it's a WW/YYYY and then allow me to do something like WW/YYYY-WW/YYYY ?
Re the original entry, I can't see an apostrophe but as it was a data download from another system and exported into Excel that might explain it.
Thanks again for your time.

 

[Joe4]

Whoops, sorry, I totally misread your question.

Leave the values as text. Then if you have a value like "02/2022" in cell A1 and "47/2021" in cell A2, and want to get the number of weeks, try this formula:

=((RIGHT(A1,4)*52)+LEFT(A1,2)) - ((RIGHT(A2,4)*52)+LEFT(A2,2))