Nov 17 2021 12:07 AM - edited Nov 17 2021 12:08 AM
Nov 17 2021 12:11 AM
SolutionCould you please illustrate on small sample file what you try to do.
Nov 17 2021 12:29 AM
@Sergei Baklan i send the excel so if its possible to see it
Nov 17 2021 01:32 AM
Thank you. Formulae could be
SUM TRUE:
=SUMPRODUCT( ($B$2:$B$6 - $A$2:$A$6)*$C$2:$C$6 )
SUM FALSE:
=SUMPRODUCT( ($B$2:$B$6 - $A$2:$A$6)*NOT($C$2:$C$6) )
SUM TRUE/FALSE:
=G2+G3
Nov 17 2021 03:33 AM
Thank you for your help but no it didn't help because maybe i didn't expain well.By using the datedif command i have the dates D2:D6. At the next table G2 I want the result of the sum the TRUE dates which at the example are D2 ,D4 but to show the result in years Months and Days. the same at the G3 I want the result of the sum the FALSE dates D3,D5,D6 and shown as years monts and days as shown at the example, and finaly at G4 I want to sum all the TRUE - FALSE dates shown in years Monts and days together.
Nov 17 2021 03:57 AM
As variant you may add helper column with formulas above and calculate number of days in years/months/days taking difference from the calendar start.
Formula is like
=DATEDIF(0,H2,"y")&" years "&DATEDIF(0,H2,"ym")&" months "&DATEDIF(0,H2,"md")&" days"
That could be plus minus day difference since calculation depends do we have leap years or not.
To calculate literally is possible, but here also formal logic shall be defined. For example we have
0 years 6 months 25 days
1 year 5 months 07 days
---
which gives
1 years 11 months 32 days
Now we have deduct extra from days and add 1 month to months. The question is what to take as number of days in the month - 28, 29, 30 or 31.
If 30
1 years 12 months 02 days
If 31
1 years 12 months 01 days
Nov 17 2021 05:22 AM
Nov 17 2021 05:35 AM
Main question here is how many days per month to take, formulae could be different. If 30 is okay with you that's fine. But compare to formulas in previous post it could be even bigger difference with actual periods.
Nov 17 2021 12:11 AM
SolutionCould you please illustrate on small sample file what you try to do.