0

I'm not sure if this is possible but here is what I would like to do if anyone knows a formula please.

I want to show the duration (in weeks) that a Case is is/was open based on: a) between the Date Opened and TODAY (if Date Closed is blank) OR b) between the Date Opened and Date Closed (if Date Closed is not blank) OR c) leave blank if start date is blank

My columns are: A 'Case', B 'Date Opened', C 'Date Closed', D 'Duration (in weeks)'

See image for example

Thanks, Eva

enter image description here

Peregrino69
  • 4,526
  • 2
  • 22
  • 30
Eva
  • 1
  • 1
    Welcome to SuperUser! We are not a free coding service but a community of people interested in computers that are willing to share their knowledge and experience. If you have already tried something please post it here, otherwise this question might get closed. – DarkDiamond Apr 03 '23 at 18:00
  • 1
    Just to comment, explore IF , DATEDIF & TODAY functions in Excel. – patkim Apr 03 '23 at 18:38
  • 1
    Have a look at daysbetween function or https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38 – spikey_richie Apr 03 '23 at 19:03

1 Answers1

0

Please try the following formula.

=IF(B2<>"",IF(C2<>"",DATEDIF(B2,C2,"d")/7,DATEDIF(B2,TODAY(),"d")/7),"")

enter image description here

Emily
  • 3,339
  • 1
  • 4
  • 6