LinkBack URL
About LinkBacks
Hi,
Can be made these differences?
B6 = 41 anno/i - 4 mese/i (41 years / 4 month)
B7 = 42 anni - 6 mesi (42 years - 6 month)
The result in B8.
I attach the example.
max_max
Hi,
Can be made these differences?
B6 = 41 anno/i - 4 mese/i (41 years / 4 month)
B7 = 42 anni - 6 mesi (42 years - 6 month)
The result in B8.
I attach the example.
max_max
As given, I see a 2 to 3 step procedure:
1) Convert text to number
2) Compute difference of number
3) Convert number back to text (if needed).
As one who avoids text operations as much as possible, steps 1 and 3 are the difficult ones, and I would do anything I could to avoid them. My first question back to you is -- is it absolutely essential to this project to have the inputs and outputs in these text formats?
If not, I would enter the values in some real number format. I could see using a yy.mm type format (and use the DOLLARDE() and DOLLARFR() functions) or entering them as fractions (yy mm/12). With the values entered as numbers, then the difference is a simple subtraction formula.
option 1: =DOLLARFR(DOLLARDE(41.04,12)-DOLLARDE(42.06,12),12) would return 1.02 (meaning 1 year and 2 months). 41.04 and 42.06 could be entered in B6 and B7 and references used.
option 2: Enter 41 4/12 and 42 6/12 into B6 and B7. B8 is simply =B7-B6. Format all cells as 0 0/12 to get the y m/12 display.
Are either of those workable options, or is it absolutely essential to deal with the text values given in your sample sheet?
Originally Posted by shg
430 = 43 anni (43 year)
4211 = 42 anni 11 mese (42 year 11 mouth)
risultato = 0 anni - 1 mese
Result = 0 years - 1 month
-------------------------
450 = 45 anni
4211 = 42 anni 11 mese
risultato = 2 anni - 1 mese
-------------------------
438 = 43 anni 8 mesi
4211 = 42 anni 11 mese
risultato = 0 anni - 9 mese
Last edited by max_max; 07-22-2017 at 07:24 AM.
One way (a bit of a monster!!
=INT(LEFT(B7,SEARCH(" ",B7)-1)+MID(TRIM(B7),SEARCH("-",TRIM(B7))+1,3)/12-(LEFT(B6,SEARCH(" ",B6)-1)+MID(TRIM(B6),SEARCH("-",TRIM(B6))+1,3)/12))&" anno/I - "&ROUND(12*MOD(LEFT(B7,SEARCH(" ",B7)-1)+MID(TRIM(B7),SEARCH("-",TRIM(B7))+1,3)/12-(LEFT(B6,SEARCH(" ",B6)-1)+MID(TRIM(B6),SEARCH("-",TRIM(B6))+1,3)/12),1),0)&" mese/I"
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
If I were going to use these integer representations, I would make them all 4 digit instead of a mix of 3 and 4 digit, and do something very similar to the first option (using the DOLLARDE() and DOLLARFR() functions)430 = 43 anni (43 year)
4211 = 42 anni 11 mese (42 year 11 mouth)
4300 = 43 year
4211=42 year 11 month
=DOLLARFR(DOLLARDE(4300/100,12)-DOLLARDE(4211/100,12),12)*100 which would return 0001 (if formatted as "0000")
By making all entries 4 digit, you always divide/multiply by 100. With a mix of 3 and 4 digit integers, you need a test to determine whether to divide/multiply by 10, and you will run into extra trouble with the ambiguity of 1 month or 10 month (will 43.1 mean 43 years 1 month or 43 years 10 months. With a 4 digit representation, you don't have that ambiguity).
Glenn Kennedy put together that monster text based formula, so maybe you will prefer that approach on the original text representation?
Hi to all.
Gleen Kennedy your formula it's o.k.
Mrshorty's too complicated your solution, I can not translate into proper Italian.
Thank you to all.
max_max
Last edited by max_max; 07-22-2017 at 12:16 PM.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Hello mr gleen kennedy.
Is it possible to modify your formula?
In the attached file, you must do this:
in B10 = (B1 + B8)-B6
new date is?
The date in B1 is in format European " day / month / year"
I thank you in advance.
max_max
Last edited by max_max; 07-24-2017 at 03:06 PM.
Sorry I forgot that in the table in column G there is also an empty line.
I attach the new example.
max_max
This proposed solution employs Mr. Shorty's suggestion (I hope correctly) and three helper cells (C6, C8 and C10) which may be hidden for aesthetic purposes.
The formula for C6 and C8 is:Formula:
Please Login or Register to view this content.
The formula for C10 is Mr. Shorty's:Formula:Please Login or Register to view this content.
The formula for B10 is:Formula:Please Login or Register to view this content.
*Since your profile states that you are using Excel 2007 I am attaching the file as an .xlsx
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Using my monster..
Thanks you gleen and jetemc, your formulas work
max_max
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Ops glen kennedy your formula if a cell G5:G13 is empty error #VALUE
max_max
Really? Very odd, since it doesn't reference G5:G13....
Look at the picture
Ahhh.. You said if G5:G13 was blank, whereas you meant B6...
Try it now...
Hello gleen is ok now.
A greeting.
max_max
GREAT!!
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Hi Gleen Kennedy,
I saw a mistake.
Example:
B1 = 06/06/1980
B6 = 18 anno/i - 10 mese/i
B8 = 45 anni - 0 mesi
result in B10 = 01/08/2006 (error)
exact is 06/08/2006
max_max
Max_max - could you please explain how this thread is really any different to the other one? The purpose seems to be essentially the same - is it?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Who, not really.
This thread the formula finds a difference between 2/3 dates
The other finds the days / month / years between 2 dates
max_max
I never adjusted the days, as the other bits were years and months...
Use:
DATE(YEAR(B1)+INT(LEFT(B8,SEARCH(" ",B8)-1)+MID(TRIM(B8),SEARCH("-",TRIM(B8))+1,3)/12-IF(B6="",0,(LEFT(B6,SEARCH(" ",B6)-1)+MID(TRIM(B6),SEARCH("-",TRIM(B6))+1,3)/12))),MONTH(B1)+ROUND(12*MOD(LEFT(B8,SEARCH(" ",B8)-1)+MID(TRIM(B8),SEARCH("-",TRIM(B8))+1,3)/12-IF(B6="",0,(LEFT(B6,SEARCH(" ",B6)-1)+MID(TRIM(B6),SEARCH("-",TRIM(B6))+1,3)/12)),1),0),DAY(B1))
instead of
DATE(YEAR(B1)+INT(LEFT(B8,SEARCH(" ",B8)-1)+MID(TRIM(B8),SEARCH("-",TRIM(B8))+1,3)/12-IF(B6="",0,(LEFT(B6,SEARCH(" ",B6)-1)+MID(TRIM(B6),SEARCH("-",TRIM(B6))+1,3)/12))),MONTH(B1)+ROUND(12*MOD(LEFT(B8,SEARCH(" ",B8)-1)+MID(TRIM(B8),SEARCH("-",TRIM(B8))+1,3)/12-IF(B6="",0,(LEFT(B6,SEARCH(" ",B6)-1)+MID(TRIM(B6),SEARCH("-",TRIM(B6))+1,3)/12)),1),0),1)
Hi gleen seems to me right now.
Your formula in Italian is:
=DATA(ANNO(B1)+INT(SINISTRA(B8;RICERCA(" ";B8)-1)+STRINGA.ESTRAI(ANNULLA.SPAZI(B8);RICERCA("-";ANNULLA.SPAZI(B8))+1;3)/12-SE(B6="";0;(SINISTRA(B6;RICERCA(" ";B6)-1)
+STRINGA.ESTRAI(ANNULLA.SPAZI(B6);RICERCA("-";ANNULLA.SPAZI(B6))+1;3)/12)));MESE(B1)+ARROTONDA(12*RESTO(SINISTRA(B8;RICERCA(" ";B8)-1)
+STRINGA.ESTRAI(ANNULLA.SPAZI(B8);RICERCA("-";ANNULLA.SPAZI(B8))+1;3)/12-SE(B6="";0;(SINISTRA(B6;RICERCA(" ";B6)-1)+STRINGA.ESTRAI(ANNULLA.SPAZI(B6);RICERCA("-";ANNULLA.SPAZI(B6))+1;3)/12));1);0);GIORNO(B1))
Thank you.
max_max
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks