• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

kindly help to manage year and month in column and it's total

Irfan Aman

New Member
Dear All,

hope you must be doing well in excel :)

I would request to guide me in following. I would like to manage year and month entry in column and to manage it's sum in other column.
When I tried to manage it in number format but it is not working as per my need.

Below screen shot will help you in understanding the requirement and challenge.

73143

looking forward for your expert advice and guidance.

Regards,
Irfan Aman
 

Attachments

  • Excel Query (addition of year and month in number format).xlsx
    10.8 KB · Views: 15
Unless you you have columns A and B formatted as Text you'll not be able to tell the difference between 1 year and 1 month and 1 year and 10 months! It's silly.
Just seen bosco_yip's post which falters when it tries to add 1yr and 11 months to 3yrs and 10 months.
I haven't given up …yet
 
Last edited:
Unless you you have columns A and B formatted as Text you'll not be able to tell the difference between 1 year and 1 month and 1 year and 10 months! It's silly.
Just seen bosco_yip's post which falters when it tries to add 1yr and 11 months to 3yrs and 10 months.
I haven't given up …yet
Maybe, try this longer formula instead,

=SUBSTITUTE(TEXT(A3,IF(LEN(A3)-LEN(INT(A3))=3,"0.000\/120","0.00\/120")),"."," ")+SUBSTITUTE(TEXT(B3,IF(LEN(B3)-LEN(INT(B3))=3,"0.000\/120","0.00\/120")),"."," ")

Remark : all value shall be formatted in Text (To make difference between 3.1 (3yrs & 1mth) and 3.10 (3yrs &10mths)

73151
 
I suspect the OP wants to see 5.9 rather than 5.75, so a tweak to yours bosco_yip:
Code:
=SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(SUBSTITUTE(TEXT(A5,IF(LEN(A5)-LEN(INT(A5))=3,"0.000\/120","0.00\/120")),"."," ")+SUBSTITUTE(TEXT(B5,IF(LEN(B5)-LEN(INT(B5))=3,"0.000\/120","0.00\/120")),"."," "),"# ?/12"),"/12",""))," ",".")
but this is getting silly. (Also it fails when adding periods that sum to less than 1 year… grrr)

Doing it in Power Query isn't too difficult but results in a separate table.
 
Last edited:
Another silly one to test:
Code:
=IFERROR(TEXTJOIN(".",FALSE,INT((INT(A5)*12+INT(B5)*12+MID(A5,1+FIND(".",A5),10)+MID(B5,1+FIND(".",B5),10))/12),MOD((INT(A5)*12+INT(B5)*12+MID(A5,1+FIND(".",A5),10)+MID(B5,1+FIND(".",B5),10)),12)),"-")
or better:
Code:
=TEXTJOIN(".",FALSE,INT((INT(A5)*12+INT(B5)*12+IFERROR(MID(A5,1+FIND(".",A5),10),0)+IFERROR(MID(B5,1+FIND(".",B5),10),0))/12),MOD((INT(A5)*12+INT(B5)*12+IFERROR(MID(A5,1+FIND(".",A5),10),0)+IFERROR(MID(B5,1+FIND(".",B5),10),0)),12))
 
Last edited:
Another silly one to test:
Code:
=IFERROR(TEXTJOIN(".",FALSE,INT((INT(A5)*12+INT(B5)*12+MID(A5,1+FIND(".",A5),10)+MID(B5,1+FIND(".",B5),10))/12),MOD((INT(A5)*12+INT(B5)*12+MID(A5,1+FIND(".",A5),10)+MID(B5,1+FIND(".",B5),10)),12)),"-")
or better:
Code:
=TEXTJOIN(".",FALSE,INT((INT(A5)*12+INT(B5)*12+IFERROR(MID(A5,1+FIND(".",A5),10),0)+IFERROR(MID(B5,1+FIND(".",B5),10),0))/12),MOD((INT(A5)*12+INT(B5)*12+IFERROR(MID(A5,1+FIND(".",A5),10),0)+IFERROR(MID(B5,1+FIND(".",B5),10),0)),12))
oops...
I appreciate your efforts.... but somehow it is not giving the expected OP
 
A very pedantic 365 solution!

Code:
= LET(
  p, SEARCH(".", priorExperience),
  priorYrs, LEFT(priorExperience, p-1),
  priorMnths, MID(priorExperience, p+1, 2),
  s, SEARCH(".", servicePeriod),
  currentYrs, LEFT(servicePeriod, s-1),
  currentMnths, MID(servicePeriod, s+1, 2),
  years, (priorYrs+currentYrs) + QUOTIENT(priorMnths+currentMnths, 12),
  months, MOD(priorMnths+currentMnths, 12),
  IF(months, years&"."&months, years) )

Out of curiosity, how do you discriminate between 1yr1month and 1yr10months? As numbers 1.1 and 1.10 are the same number.
 
Out of curiosity, how do you discriminate between 1yr1month and 1yr10months? As numbers 1.1 and 1.10 are the same number.
Quite, I pointed this out in msg#3 and is why I think that all the colums have to be text only.

It's crazy to do it this way because you lose all the advantages of simple arithmetic on the dates/durations.
 
A very pedantic 365 solution!

Code:
= LET(
  p, SEARCH(".", priorExperience),
  priorYrs, LEFT(priorExperience, p-1),
  priorMnths, MID(priorExperience, p+1, 2),
  s, SEARCH(".", servicePeriod),
  currentYrs, LEFT(servicePeriod, s-1),
  currentMnths, MID(servicePeriod, s+1, 2),
  years, (priorYrs+currentYrs) + QUOTIENT(priorMnths+currentMnths, 12),
  months, MOD(priorMnths+currentMnths, 12),
  IF(months, years&"."&months, years) )

Out of curiosity, how do you discriminate between 1yr1month and 1yr10months? As numbers 1.1 and 1.10 are the same number.

Hi Peter,

Thanks for your reply.
As a workaround, as of now I am using 1.01 for one year and one month and 1.10 for one year and ten months.
Kindly suggest if any better way is there to manage this requirement.
 
Code:
=INT((INT(B2)*12+MOD(B2,1)*100+INT(A2)*12+MOD(A2,1)*100)/12)+MOD(INT(B2)*12+MOD(B2,1)*100+INT(A2)*12+MOD(A2,1)*100,12)/100
 
Back
Top