Dated if Formula

[ChetanPuri]

Hi Excel Team,

Just need help with the Following , I am trying to creat formula, for example in column K I want to calculate number of weeks between 2 dates as in column I 12 & J 12, and then in column k 12 I have a reporting date 09 Feb 2024 , which is higher than date in column J (11/12/2023) and similarly on the next row end date is higher than the reporting date. What I want to create is 2 calculate number of weeks based on 2 criteria for example if start date is less than 01 July 2023, then it should calculate number of weeks between 01 July 2023 and end date , but if the end date is higher than the reporting date than it should calculate the no. of weeks between 01 July 2023 and Reporting Date. Any help on this will be highly appreciated.

 

[kvsrinivasamurthy]

Try
=(MIN(J12,K12)-I12)/7

 

[ChetanPuri]

Try
=(MIN(J12,K12)-I12)/7

thank you for your response but
Sorry it doesn't work as it gives me 40.285, whereas it should calculate 23.29 weeks. as 09 Feb 2024 is greater than 11/12 /2023

 

[kvsrinivasamurthy]

Try

=(MIN(J12,K12)-MAX(I12,DATE(YEAR(I12),7,1)))/7