SOLVED

Summing the algorithm datedif

%3CLINGO-SUB%20id%3D%22lingo-sub-2973845%22%20slang%3D%22en-US%22%3ESumming%20the%20algorithm%20datedif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2973845%22%20slang%3D%22en-US%22%3EHELLO%3CBR%20%2F%3EI%20HAVE%20A%20PROBLEM%20OF%20CALCULATION!%20I%20USED%20THE%20FUNCTION%20DATEDIF%20TO%20FIND%20THE%20DIFFERENCE%20BETWEEN%20MANY%20DIFFRENT%20DATES%20AND%20THE%20RESULT%20IS%20THESESONE%20%22%20....%20years...months..days%20%22%20FOR%20EACH%20CALCULATE%20WHICH%20IS%20NORMAL.%20THE%20PROBLEM%20IS%20THAT%20I%20WANT%20TO%20USE%20THE%20FUNCTION%20IF%20SOIF%20THE%20VALID%20IS%20TRUE%20IT%20CAN%20BE%20SUMMED%20I%20WANT%20TO%20SUM%20ALL%20THESE%20%22.....years....months....days%22%20BUT%20I%20CANNOT%20FIND%20A%20WAY%20TO%20DO%20THAT%3CBR%20%2F%3E%3CBR%20%2F%3E.....years..........months.........days%22%20false%3CBR%20%2F%3E%3CBR%20%2F%3E.....years..........months.........days%22%20true%3CBR%20%2F%3E%3CBR%20%2F%3E.....years..........months.........days%22%20true%3CBR%20%2F%3E%3CBR%20%2F%3Etotal%20years%20total%20months%20total%20days%20if%20true%20sum!!%3CBR%20%2F%3E%3CBR%20%2F%3ETHANK%20YOU%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2973845%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2973882%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20the%20algorithm%20datedif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2973882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1221141%22%20target%3D%22_blank%22%3E%40lkorahais%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20illustrate%20on%20small%20sample%20file%20what%20you%20try%20to%20do.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2973940%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20the%20algorithm%20datedif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2973940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bi%20send%20the%20excel%20so%20if%20its%20possible%20to%20see%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2974140%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20the%20algorithm%20datedif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2974140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1221141%22%20target%3D%22_blank%22%3E%40lkorahais%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you.%20Formulae%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3ESUM%20TRUE%3A%0A%3DSUMPRODUCT(%20(%24B%242%3A%24B%246%20-%20%24A%242%3A%24A%246)*%24C%242%3A%24C%246%20)%0A%0ASUM%20FALSE%3A%0A%3DSUMPRODUCT(%20(%24B%242%3A%24B%246%20-%20%24A%242%3A%24A%246)*NOT(%24C%242%3A%24C%246)%20)%0A%0ASUM%20TRUE%2FFALSE%3A%0A%3DG2%2BG3%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2974458%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20the%20algorithm%20datedif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2974458%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20help%20but%20no%20it%20didn't%20help%20because%20maybe%20i%20didn't%20expain%20well.By%20using%20the%20datedif%20command%20i%20have%20the%20dates%20D2%3AD6.%20At%20the%20next%20table%20G2%20I%20want%20the%20result%20of%20the%20sum%20the%20TRUE%20dates%20which%20at%20the%20example%20are%20D2%20%2CD4%20but%20to%20show%20the%20result%20in%20years%20Months%20and%20Days.%20the%20same%20at%20the%20G3%20I%20want%20the%20result%20of%20the%20sum%20the%20FALSE%20dates%20D3%2CD5%2CD6%20and%20shown%20as%20years%20monts%20and%20days%20as%20shown%20at%20the%20example%2C%20and%20finaly%20at%20G4%20I%20want%20to%20sum%20all%20the%20TRUE%20-%20FALSE%20dates%20shown%20in%20years%20Monts%20and%20days%20together.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
HELLO
I HAVE A PROBLEM OF CALCULATION! I USED THE FUNCTION DATEDIF TO FIND THE DIFFERENCE BETWEEN MANY DIFFRENT DATES AND THE RESULT IS THESESONE " .... years...months..days " FOR EACH CALCULATE WHICH IS NORMAL. THE PROBLEM IS THAT I WANT TO USE THE FUNCTION IF SOIF THE VALID IS TRUE IT CAN BE SUMMED I WANT TO SUM ALL THESE ".....years....months....days" BUT I CANNOT FIND A WAY TO DO THAT

.....years..........months.........days" false

.....years..........months.........days" true

.....years..........months.........days" true

total years total months total days if true sum!!

THANK YOU
7 Replies
best response confirmed by lkorahais (New Contributor)
Solution

@lkorahais 

Could you please illustrate on small sample file what you try to do.

@Sergei Baklan i send the excel so if its possible to see it

@lkorahais 

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

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.

@Sergei Baklan 

@lkorahais 

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.

image.png

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

 

THANK YOU VERY MUCH I THINK I HAVE STARTED A WAY TO FIGURE OUT
BUT AS YOU SAID BEFORE I WILL HAVE A MISMUCH WITH THE DAYS 28-29-30-31 DAYS TO FIND A SOLLUTION I USED OTHER ALGORITHM

=INT(G2/365)&" Year(s), "&INT(MOD(G2;365)/30)&" Month(s) and "&MOD(MOD(G2;365);30)&" Day(s)"
SO I COULD FIND A WAY THANK YOU

@lkorahais 

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.