Dear all,

I need to be able to calculate the number of days between two dates (and I have thousands of dates). Both dates may be a BC or AD date or one date will be a BC date and the other and AD date. In addition, the dates are in Excel columns in a particular format as following examples:

Examples
-1898-Jan-23
-1898-Jul-18
-1898-Dec-14
2001-Feb-13
2001-Aug-09
2002-Feb-03
Thanks for the help.
Luciano

Hi Luciano,

Am Thu, 18 Jul 2013 03:57:46 -0700 (PDT) schrieb Luciano:

Examples
-1898-Jan-23
-1898-Jul-18
-1898-Dec-14
2001-Feb-13
2001-Aug-09
2002-Feb-03

in Excel you can calculate with dates = 01.01.1900
Every 400 years the years are equal. Therefore add 400 (or 800, 1200)
years to your strings and the calculate the difference with DateDif
Your strings in column A then try:
=DATE(MID(A1,2,4)+400,SEARCH(MID(A1,7,3),"--janfebmaraprmayjunjulaugsepoctnovdec")/3,DAY(RIGHT(A1,2)))


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Dear Claus,
Thank you for your post, but unfortunately for some reason your suggestion did not work here.
Luciano



Em quinta-feira, 18 de julho de 2013 08h53min16s UTC-3, Claus Busch escreveu:
Hi Luciano,



Am Thu, 18 Jul 2013 03:57:46 -0700 (PDT) schrieb Luciano:



Examples

-1898-Jan-23

-1898-Jul-18

-1898-Dec-14

2001-Feb-13

2001-Aug-09

2002-Feb-03



in Excel you can calculate with dates = 01.01.1900

Every 400 years the years are equal. Therefore add 400 (or 800, 1200)

years to your strings and the calculate the difference with DateDif

Your strings in column A then try:

=DATE(MID(A1,2,4)+400,SEARCH(MID(A1,7,3),"--janfebmaraprmayjunjulaugsepoctnovdec")/3,DAY(RIGHT(A1,2)))





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hi Luciano,

Am Thu, 18 Jul 2013 07:58:30 -0700 (PDT) schrieb Luciano:

Thank you for your post, but unfortunately for some reason your suggestion did not work here.

please look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Datedif"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Dear Claus,
Thank you!
Have you some suggestion to express the results in column C in days, hours, minutes, etc instead integrer numbers of months or years? In addition it would be nice to me express exactly such units in each column like C = years, D = months, E = days, F = hours.
For example using also fractional numbers like for days 365.25 or for years 1.23
Thanks in advance,
Luciano

Em quinta-feira, 18 de julho de 2013 12h10min42s UTC-3, Claus Busch escreveu:
Hi Luciano,



Am Thu, 18 Jul 2013 07:58:30 -0700 (PDT) schrieb Luciano:



Thank you for your post, but unfortunately for some reason your suggestion did not work here.



please look he

https://skydrive.live.com/#cid=9378A...121822A3%21326

for the workbook "Datedif"





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hi Luciano,

Am Thu, 18 Jul 2013 08:38:00 -0700 (PDT) schrieb Luciano:

Have you some suggestion to express the results in column C in days, hours, minutes, etc instead integrer numbers of months or years? In addition it would be nice to me express exactly such units in each column like C = years, D = months, E = days, F = hours.
For example using also fractional numbers like for days 365.25 or for years 1.23

do you want the total difference in all the formats or do you want the
result splitted in these formats?
Have another look for the workbook. There are 2 sheets with 2
suggestions.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2