Sum up date difference between multiple dates

[anton980]

Hello, everyone!

I am trying to come up with a formula to calculate the total number of days derived from multiple date differences.

I have 2 columns: Submitted Date (column J) and Closed Date (column L). Unfortunately, I cannot add any more columns to calculate individual difference per line, as the data will be coming from an auto-generated Excel spreadsheet in a format like the snapshot below:

snapshot.jpg

So far, the best I could come up with is this:
=IF(OR([exported.xlsx]Sheet1!$L$4="", [exported.xlsx]Sheet1!$J$4=""),"", DATEDIF([exported.xlsx]Sheet1!$J$4, [exported.xlsx]Sheet1!$L$4, "d"))
But this only calculates the days between a single set of 2 dates.

I am really hoping there's a way to indicate an entire range and plus all the days up in a single formula.

Thank you very much in advance!

[TMS]

You can probably use SUMPRODUCT.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[anton980]

You can probably use SUMPRODUCT.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Thank you, you're right! Attaching a sample workbook. Sheet1 contains sample data, Sheet2 contains what the outcome of the formula should show.

[Gregb11]

I don't know if this will work on V2019. It works for MS365. You may have to press Shift, Ctrl, and Enter at the same time.

=SUM(IF(Sheet1!$L$2:$L$30="","",ROUND(Sheet1!$L$2:$L$30-Sheet1!$J$2:$J$30,0)))

[TMS]

Try:

Please Login or Register  to view this content.

[TMS]

Think that would have to be

Please Login or Register  to view this content.

[TMS]

And, just for fun, if you have an Excel 365 subscription:

Please Login or Register  to view this content.

[TMS]

Is this resolved now?

[anton980]

Hello everyone! Thank you very much for your help!

Ended up going with a variation of =SUMPRODUCT(--(L2:L30<>"")*(INT(L2:L30)-INT(J2:J30))) -

Greb11: much appreciate your formula. The result of it was a bit off, possibly due to rounding, as I have several thousand lines involved in calculation. Similar was with TMS's second solution.

Again, thank you both! It was super quick! Marked the thread as resolved!

[TMS]

You're welcome. Thanks for the rep.