Value by the difference between dates

[ForeverUnited]

Hi all,

This is my sheet and I need to do two things once column E has been manually populated with a date (just as column C has been).

I need to auto-populate columns D and F.

The value in column D will depend on the value of C.

The value in column F will depend on the value of E.

If the dates in C/E are between (and including):
03/01/22 and 09/01/22 then the value of D/F = 1
10/01/22 and 16/01/22 then the value = 2
17/01/22 and 23/01/22 then the value = 3
24/01/22 and 30/01/22 then the value = 4
31/01/22 and 06/02/22 then the value = 5
07/02/22 and 13/02/22 then the value = 6
14/02/22 and 20/02/22 then the value = 7
21/02/22 and 27/02/22 then the value = 8
28/02/22 and 06/03/22 then the value = 9
07/03/22 and 13/03/22 then the value = 10
14/03/22 and 20/03/22 then the value = 11
21/03/22 and 27/03/22 then the value = 12
28/03/22 and 03/04/22 then the value = 13
04/04/22 and 10/04/22 then the value = 14
11/04/22 and 17/04/22 then the value = 15
18/04/22 and 24/04/22 then the value = 16
25/04/22 and 01/05/22 then the value = 17
02/05/22 and 08/05/22 then the value = 18
09/05/22 and 15/05/22 then the value = 19
16/05/22 and 22/05/22 then the value = 20
23/05/22 and 29/05/22 then the value = 21

To clarify, D only depends on the value in C.
F only depends on the value in E.

View attachment 66301

Thanks in advance if any help can be provided!

 

[traveler4]

Your link does not work.

It would be helpful if you could provide the sample data (and the expected results) in a form that helpers can copy to test with. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

• Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest January 2021 v 2.0 )
• If posting vba code, please look at the VBA, RICH, </> and >_ buttons above the Reply window. More details here
• Update your Account details (click your user name at the top right of the forum) so we know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• Suggested reading: FAQ (especially Rules and Guidelines) & Mark as solution

 

[ForeverUnited]

Apologies, I have re uploaded.

 

[Fluff]

How about

=IF(C2="","",ISOWEEKNUM(C2))

 

[ForeverUnited]

That's worked perfectly, thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback.