Need different between date

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
225
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
Is this what you mean? If not, more detail and the exact expected result(s) please.

Cell Formulas
RangeFormula
G2:G4G2=DATEDIF(B2,B3,"ym")&" months "&DATEDIF(B2,B3,"md")&" Days "
D2:D5D2=DATEDIF(B2,TODAY(),"y")&" years "&DATEDIF(B2,TODAY(),"ym")&" months "&DATEDIF(B2,TODAY(),"md")&" Days "
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
225
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Is this what you mean? If not, more detail and the exact expected result(s) please.

Cell Formulas
RangeFormula
G2:G4G2=DATEDIF(B2,B3,"ym")&" months "&DATEDIF(B2,B3,"md")&" Days "
D2:D5D2=DATEDIF(B2,TODAY(),"y")&" years "&DATEDIF(B2,TODAY(),"ym")&" months "&DATEDIF(B2,TODAY(),"md")&" Days "


Hey Peter,

Thanks for your help on this but i need to calculation on Column D (D2-D3) as i have huge data similar to Column D.

Thanks.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
but i need to calculation on Column D (D2-D3) as i have huge data similar to Column D.
Why specifically from column D as those results come from column B anyway?

Forgetting the method for a moment, are the results in my sheet the results you want?
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
225
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Why specifically from column D as those results come from column B anyway?

Forgetting the method for a moment, are the results in my sheet the results you want?


Hello Peter,

the result has come perfectly. but in my data base i have a data in Column D format not in Column B formate.

so just wanted to check it is possible to do the calculation via D coliumn

Thanks in advance:)

Thanks.
Sanjeev
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
wanted to check it is possible to do the calculation via D coliumn
To consider that, could you post the column D data again but include your expected results, worked out manually, and entered into column E and explain how you went about it manually?
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
225
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

To consider that, could you post the column D data again but include your expected results, worked out manually, and entered into column E and explain how you went about it manually?

Sure Peter

below is data

Book1
ABC
1Year spend in Org.Different
214 years 11 months 12 Days#VALUE!
314 years 9 months 18 Days#VALUE!
414 years 0 months 25 Days#VALUE!
513 years 11 months 2 Days
Sheet1
Cell Formulas
RangeFormula
C2:C4C2=DATEDIF(A2,A3,"ym")&" months "&DATEDIF(A2,A3,"md")&" Days "


Thanks in advance.
Regards,
Sanjeev
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
225
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sorry, but I asked for the actual results you want in column C, not a formula that does not do what you want.
Here is the data in Column C

Book1
ABC
1Year spend in Org.Different
214 years 11 months 12 Days1 month 25 days
314 years 9 months 18 Days8 month 21 days
414 years 0 months 25 Days1 month 21 days
513 years 11 months 2 Days
Sheet1


Thanks in advance
Sanjeev
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
:confused: I don't see how you came up with some of those results - & remember that I did ask you ..
and explain how you went about it manually

Presumably you have made an assumption about the number of days in a month?

My calculation (using 30 days for a month) of 8 months 21 days after 14 years 0 months 25 days would be 14 years 9 months 16 days yet A3 has 18 days. The only way to end up with 18 days that I can see is to assume 28 days in a month which would normally be incorrect as I see it.

My calculation of 1 month 21 days after 13 years 11 months 2 days would be 14 years 0 months 23 days. Again, the way to end up with 25 days as shown in A4 would be to use 28 days for a month.

However, if 28 days was used for a month the first result would be 1 month 22 days not 1 month 25 days that you have shown.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,682
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top