Subtracting AD and/or BC dates
 

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

Subtracting AD and/or BC dates
 

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

Subtracting AD and/or BC dates
 

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

Subtracting AD and/or BC dates
 

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

Subtracting AD and/or BC dates
 

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

Subtracting AD and/or BC dates
 

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

Subtracting AD and/or BC dates
 

Dear Claus,
Fantastic! I would like to use the total, but I'm sure that the splitted will also very useful.
Since I have some negative dates (BC), I observed that in these cases there are some problems in the conversions (in your sheet theses dates are typed like ('-1898-Jan-23). Do you have some idea to solve that since I have thousands of dates to compare?
Thanks in advance,
Luciano

Em quinta-feira, 18 de julho de 2013 13h38min04s UTC-3, Claus Busch escreveu:
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

Subtracting AD and/or BC dates
 

These questions are because my dates are ranging from -1999 to 3000 (2000 BCE to 3000 CE):
-1999-Jun-25
-1999-Nov-21
-1997-May-06
-1996-Apr-24
-1995-Mar-16
-1995-Sep-08
-1995-Oct-07
-1994-Aug-28
-1992-Aug-06
-1991-Jun-27
-1991-Dec-21
-1990-Jun-16
-1989-Jun-05
-1989-Nov-30
-1988-Apr-26
-1987-Apr-15
-1986-Sep-28
-1985-Mar-25
-1985-Sep-18
-1984-Feb-13
-1984-Sep-06
-1983-Feb-01
-1983-Jul-28
-1982-Jan-21
-1982-Jul-18
-1981-Jul-07
-1980-May-26
-1980-Nov-20
-1979-Nov-09
-1978-May-06
-1978-Oct-29
-1977-Apr-25
-1977-Oct-19
-1976-Mar-15
-1976-Sep-08
-1975-Mar-04
-1975-Aug-28
-1974-Feb-21
-1973-Jul-08
-1972-Jun-26
-1972-Dec-21
-1971-Jun-16
-1970-Jun-05
-1970-Oct-31
-1969-Apr-27
-1969-Oct-20
-1968-Apr-15
-1968-Oct-08
-1967-Apr-05
-1966-Aug-19
-1966-Sep-18
-1965-Feb-12
-1965-Aug-09
-1964-Feb-01
-1963-Jan-21
-1963-Dec-12
-1962-Jun-07
-1962-Dec-02
-1961-May-27
-1961-Nov-21
-1960-Nov-09
-1959-Apr-06
-1959-May-06
-1959-Sep-29
-1958-Mar-26
-1957-Mar-15
-1957-Sep-09
-1956-Mar-04
-1956-Aug-28
-1955-Jan-22
-1955-Jul-19
-1954-Jan-12
-1954-Jul-08
-1953-Jan-02
-1953-Dec-22
-1952-May-17
-1952-Jun-15
-1952-Nov-10
-1951-Oct-30
-1950-Apr-26
-1949-Oct-09
-1948-Mar-05
-1948-Aug-30
-1947-Feb-22
-1946-Aug-08
-1945-Feb-01
-1945-Jul-28
-1945-Dec-24
-1943-Jun-06
-1943-Dec-01
-1942-May-27
-1941-Apr-17
-1941-Oct-11
-1941-Nov-09
-1940-Apr-06
-1940-Sep-29
-1939-Mar-26
-1938-Sep-08
-1937-Mar-04
-1936-Jan-23
-1935-Jan-12
-1935-Jul-07
-1934-Jan-02
-1933-May-18
-1932-Oct-30
-1931-Apr-26
-1931-Oct-20
-1930-Mar-17
-1930-Apr-15
-1930-Oct-09
-1929-Mar-06
-1929-Aug-30
-1928-Feb-23
-1928-Aug-18
-1927-Aug-07
-1926-Feb-02
-1926-Jun-28
-1926-Jul-28
-1926-Dec-23
-1925-Dec-12
-1924-Jun-06
-1924-Nov-30
-1923-May-27
-1923-Nov-20
-1922-Oct-11
-1921-Sep-30
-1920-Mar-25
-1919-Feb-13
-1919-Mar-15
-1919-Aug-09
-1918-Jul-29
-1917-Jan-23
-1917-Jul-19
-1916-Jul-07
-1916-Dec-02
-1915-May-29
-1915-Nov-21
-1914-May-18
-1914-Nov-10
-1913-May-07
-1912-Apr-25
-1912-Sep-20
-1911-Sep-10
-1910-Mar-05
-1910-Aug-30
-1909-Feb-23
-1909-Aug-19
-1908-Jan-14
-1908-Jul-09
-1907-Jan-03
-1907-Jun-28
-1907-Dec-23
-1906-Dec-12
-1905-Jun-07
-1905-Nov-01
-1904-Apr-27
-1903-Apr-16
-1903-Oct-11
-1902-Apr-05
-1902-Sep-30
-1901-Feb-25
-1901-Aug-21
-1901-Sep-19
-1900-Feb-14
-1900-Aug-09
-899-Apr-17
-899-Oct-10
-898-Apr-06
-898-Sep-29
-897-Mar-27
-896-Mar-15
-896-Sep-07
-895-Jul-30
-894-Jan-22
-892-Jan-02
-892-May-28
-892-Jun-26
-892-Nov-22
-891-May-17
-891-Nov-11
-890-Oct-31
-889-Apr-26
-888-Mar-16
-887-Mar-05
-887-Aug-29
-886-Feb-23
-886-Aug-19
-885-Aug-08
-884-Dec-23
-883-Dec-12
-882-Jun-06
-882-Dec-01
-881-Oct-21
-880-Apr-16
-880-Oct-10
-879-Sep-29
-877-Feb-13
-876-Feb-03
-876-Jul-29
-875-Jan-22
-875-Jul-18
-874-Jul-07
-873-May-28
-872-May-17
-871-Oct-30
-870-Sep-20
-868-Aug-29
-867-Feb-22
-867-Aug-19
-866-Jan-13
-866-Jul-09
-865-Jun-28
-865-Dec-23
-864-Jun-17
-864-Dec-12
-863-May-08
-863-Jun-06
-862-Apr-28
-861-Apr-17
-861-Oct-11
-860-Apr-05
-860-Sep-29
-859-Feb-24
-859-Aug-20
-858-Aug-10
-857-Feb-03
-857-Jul-30
-856-Jan-23
-856-Jul-18
-856-Dec-13
-855-Jun-08
-855-Dec-02
-854-May-28
-854-Nov-21
-853-May-18
-853-Nov-10
-852-May-07
-852-Sep-30
-851-Mar-27
-851-Sep-20
-850-Mar-16
-850-Sep-10
-849-Mar-05
-848-Jan-25
-848-Jul-19
-847-Jan-13
-847-Jul-09
-845-May-19
-845-Jun-18
-845-Nov-12
-844-May-08
-844-Oct-31
-843-Apr-27
-842-Apr-17
-842-Oct-11
-841-Sep-01
-840-Feb-24
-838-Feb-03
-838-Dec-25
-837-Dec-14
-836-Jun-08
-836-Dec-02
-835-May-28
-834-Apr-18
-833-Apr-07
-833-Oct-01
-832-Mar-26
-832-Sep-20
-831-Sep-09
-830-Jul-31
-829-Jan-25
-828-Jan-14
-828-Jul-08
-827-Jan-02
-827-Nov-22
-826-May-19
-826-Nov-12
-825-May-09
-825-Nov-01
-823-Mar-17
-822-Mar-07
-822-Aug-31
-821-Feb-24
-821-Aug-20
-820-Aug-08
-819-Jun-29
-817-Jun-09
-817-Dec-02
-816-Apr-29
-816-May-28
-816-Oct-22
-815-Apr-18
-814-Apr-07
-814-Oct-01
-813-Mar-27
-813-Sep-21
-812-Feb-15
-811-Jul-30
-810-Jan-24
-810-Jul-20
-809-Jan-14
-809-Jul-09
-808-May-30
-808-Nov-22
-807-Nov-12
-806-May-08
-806-Nov-01
-805-Mar-29
-805-Apr-27
-805-Sep-22
-804-Sep-10
-803-Mar-07
-803-Aug-30
-802-Feb-24
-802-Aug-20
-801-Jan-15
-801-Jul-11
-800-Jan-04
-800-Jun-29
-800-Dec-23
2901-Feb-24
2901-Aug-21
2902-Feb-13
2903-Jan-04
2903-Feb-03
2903-Jun-30
2904-Jun-18
2904-Dec-13
2905-Dec-02
2906-Apr-29
2906-May-29
2906-Nov-21
2908-Apr-07
2908-Oct-01
2909-Mar-27
2909-Sep-20
2910-Feb-15
2910-Mar-16
2910-Aug-11
2911-Aug-01
2912-Jan-25
2912-Jul-20
2913-Jan-13
2914-Nov-23
2915-May-20
2915-Nov-12
2916-May-08
2916-Oct-31
2917-Apr-28
2917-Sep-22
2918-Mar-18
2918-Sep-11
2919-Mar-07
2920-Feb-24
2920-Aug-20
2921-Jan-15
2921-Jul-11
2922-Jan-04
2922-Jun-30
2922-Dec-25
2923-Jun-19
2923-Dec-14
2924-May-10
2924-Dec-02
2925-Apr-29
2925-Oct-22
2926-Apr-19
2927-Apr-08
2927-Oct-02
2928-Mar-27
2928-Sep-20
2929-Feb-14
2931-Jan-25
2931-Jul-20
2931-Dec-16
2932-Jun-09
2932-Dec-03
2933-Nov-22
2934-May-20
2934-Nov-12
2935-Apr-09
2935-Nov-01
2936-Mar-28
2936-Sep-22
2937-Mar-17
2937-Sep-11
2938-Aug-31
2939-Feb-24
2940-Jan-16
2941-Jun-30
2942-Jun-19
2943-May-11
2943-Nov-03
2944-Oct-22
2946-Mar-08
2946-Oct-02
2947-Feb-26
2947-Aug-22
2948-Feb-15
2948-Aug-10
2949-Jul-30
2950-Jan-24
2950-Jun-21
2951-Jun-10
2953-May-19
2953-Oct-13
2953-Nov-12
2954-Apr-09
2955-Mar-29
2955-Sep-22
2956-Mar-17
2956-Sep-11
2957-Feb-05
2957-Aug-01
2958-Jul-21
2959-Jan-15
2960-Jan-04
2960-May-31
2960-Jun-30
2960-Dec-23
2962-May-10
2962-Nov-03
2963-Apr-29
2963-Oct-23
2964-Mar-19
2964-Apr-17
2964-Sep-12
2965-Sep-01
2966-Feb-26
2966-Aug-22
2967-Feb-15
2967-Aug-11
2968-Jul-30
2968-Dec-25
2969-Jun-21
2969-Dec-14
2970-Jun-10
2970-Dec-03
2971-May-31
2971-Oct-25
2972-Oct-13
2973-Apr-08
2973-Oct-03
2974-Mar-28
2975-Feb-17
2975-Aug-12
2976-Feb-06
2976-Aug-01
2977-Jan-26
2977-Jul-21
2978-Jan-15
2979-Jan-04
2979-Jun-01
2979-Nov-24
2980-May-20
2981-May-09
2981-Nov-03
2982-Apr-29
2982-Sep-24
2982-Oct-23
2983-Mar-19
2985-Feb-26
2986-Feb-15
2986-Jul-12
2986-Aug-11
2987-Jan-05
2987-Dec-25
2988-Jun-20
2988-Dec-14
2989-Dec-03
2990-Apr-30
2990-Oct-25
2991-Apr-19
2991-Oct-14
2992-Oct-02
2993-Mar-28
2993-Aug-23
2993-Sep-21
2994-Feb-17
2995-Aug-01
2996-Jul-21
2997-Jun-11
2997-Dec-05
2998-Jun-01
2998-Nov-24
2999-May-21
3000-Nov-03


Em quinta-feira, 18 de julho de 2013 13h54min41s UTC-3, Luciano escreveu:
Dear Claus,

Fantastic! I would like to use the total, but I'm sure that the splitted will also very useful.

Since I have some negative dates (BC), I observed that in these cases there are some problems in the conversions (in your sheet theses dates are typed like ('-1898-Jan-23). Do you have some idea to solve that since I have thousands of dates to compare?

Thanks in advance,

Luciano



Em quinta-feira, 18 de julho de 2013 13h38min04s UTC-3, Claus Busch escreveu:

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

Subtracting AD and/or BC dates
 

Hallo Luciano,

Am Thu, 18 Jul 2013 09:54:41 -0700 (PDT) schrieb Luciano:

Since I have some negative dates (BC), I observed that in these cases there are some problems in the conversions (in your sheet theses dates are typed like ('-1898-Jan-23). Do you have some idea to solve that since I have thousands of dates to compare?

to have this format for the negative dates you have inserted a space in
front of the string. Replace that space with an apostroph.
Try this macro (mosify to suit):

Sub ReplaceSpace()
Range("A2:A50000").Replace " ", "'"
End Sub


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

Subtracting AD and/or BC dates
 

Hi Luciano,

Am Thu, 18 Jul 2013 10:45:10 -0700 (PDT) schrieb Luciano:

I understood that, But in my case I do not have spaces in front of the date number. When one date felt on an year and the other date on another year the calculation display negative values instead the number of years, like:
Calendar Date Adapted Years Month Days Hours Minutes
-1999-Jun-25 6/25/2399
-1999-Nov-21 11/21/2399 0.408 4.895 149 3576 214560
-1997-May-06 5/6/2397 -2.543 -30.522 -929 -22296 -1337760

you have to test whether B3B2 or B3<B2
Have another look I changed the formulas


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

Subtracting AD and/or BC dates
 

Hi Claus,
The formulas were perfect until the date below and after that they displayed an error:
-1000-Sep-17 9/17/3300 1.112 13.339 406 9744 584640
-999-Mar-13 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!
-999-Sep-07 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!
-998-Aug-27 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!
-997-Feb-21 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!
-996-Jan-12 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!
-996-Dec-31 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!
-995-Jun-25 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!

What could it be happening?
Thanks in advance,
Luciano

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



Am Thu, 18 Jul 2013 10:45:10 -0700 (PDT) schrieb Luciano:



I understood that, But in my case I do not have spaces in front of the date number. When one date felt on an year and the other date on another year the calculation display negative values instead the number of years, like:

Calendar Date Adapted Years Month Days Hours Minutes

-1999-Jun-25 6/25/2399

-1999-Nov-21 11/21/2399 0.408 4.895 149 3576 214560

-1997-May-06 5/6/2397 -2.543 -30.522 -929 -22296 -1337760



you have to test whether B3B2 or B3<B2

Have another look I changed the formulas





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Subtracting AD and/or BC dates
 

Hi Luciano,

Am Thu, 18 Jul 2013 11:43:57 -0700 (PDT) schrieb Luciano:

-996-Dec-31 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!
-995-Jun-25 #VALOR! #VALOR! #VALOR! #VALOR! #VALOR! #VALOR!

What could it be happening?

you have years with 4 and with 3 digits
Have another look for the workbook


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

Subtracting AD and/or BC dates
 

Hi Luciano,

Am Thu, 18 Jul 2013 21:58:12 +0200 schrieb Claus Busch:

Have another look for the workbook

I forgot the 1900 years to Excel year 0


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

Subtracting AD and/or BC dates
 

Hi Claus,
Absolutely perfect! I am indebted to you!
Thank you!
Luciano

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



Am Thu, 18 Jul 2013 21:58:12 +0200 schrieb Claus Busch:



Have another look for the workbook



I forgot the 1900 years to Excel year 0





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Subtracting AD and/or BC dates
 

Dear Claus,
I have checked all the data and these dates showed some calculation problem:
0-Jan-09
0-Jul-05
1-Jun-24
1-Dec-19
2-May-14
2-Nov-08
3-May-04
3-Oct-28
4-Oct-16
5-Apr-12
5-Oct-06
6-Mar-03
6-Aug-27
7-Feb-20
7-Aug-16
8-Feb-09
8-Dec-30
9-Jan-29
9-Jun-25
10-Jun-14
10-Dec-09
12-May-23
13-Oct-07
14-Apr-03
14-Sep-26
15-Mar-24
15-Sep-16
16-Mar-12
16-Aug-06
17-Jan-30
18-Jan-20
19-Jan-09
19-Nov-30
20-Nov-19
21-May-14
21-Nov-08
22-May-04
22-Oct-28
23-Mar-25
23-Apr-24
23-Sep-17
24-Mar-13
24-Sep-06
25-Mar-02
25-Aug-27
26-Feb-20
26-Aug-16
27-Jan-11
27-Jul-07
27-Aug-05
27-Dec-31
28-Jun-25
29-Jun-14
29-Dec-09
30-May-06
30-Jun-04
30-Nov-28
31-Apr-25
31-Oct-18
32-Apr-14

Do you know what it could be?
Thanks in advance,
Luciano

Em quinta-feira, 18 de julho de 2013 19h21min15s UTC-3, Luciano escreveu:
Hi Claus,

Absolutely perfect! I am indebted to you!

Thank you!

Luciano



Em quinta-feira, 18 de julho de 2013 19h10min48s UTC-3, Claus Busch escreveu:

Hi Luciano,







Am Thu, 18 Jul 2013 21:58:12 +0200 schrieb Claus Busch:







Have another look for the workbook







I forgot the 1900 years to Excel year 0











Regards



Claus B.



--



Win XP PRof SP2 / Vista Ultimate SP2



Office 2003 SP2 /2007 Ultimate SP2

Subtracting AD and/or BC dates
 

Hi Luciano,

Am Fri, 19 Jul 2013 04:47:36 -0700 (PDT) schrieb Luciano:

30-Nov-28
31-Apr-25
31-Oct-18
32-Apr-14

have another look for the workbook


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

Subtracting AD and/or BC dates
 

Thank you Claus! There was a cell format problem with my sheet.
Best regards,
Luciano

Em sexta-feira, 19 de julho de 2013 10h01min27s UTC-3, Claus Busch escreveu:
Hi Luciano,



Am Fri, 19 Jul 2013 04:47:36 -0700 (PDT) schrieb Luciano:



30-Nov-28

31-Apr-25

31-Oct-18

32-Apr-14



have another look for the workbook





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Subtracting AD and/or BC dates
 

Hi Luciano,

Am Fri, 19 Jul 2013 07:17:52 -0700 (PDT) schrieb Luciano:

There was a cell format problem with my sheet.

you know that this calculation is not exactly because there have been
some calendar changes in your period?


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

Subtracting AD and/or BC dates
 

Hi Claus,
Yes, but I believe that it is a good approximation. Now I have a new "problem" to solve that is look for a specific sum of values in a column that match a specific criterium. For example: Look for the situations for which the value is 30 and type this value in an adjacent column:
5.0
18.0
12.0 30.0
11.0
6.0
1.0
11.0
24.0
11.0
6.0
6.0
12.0
6.0 30.0
5.0
12.0

Subtracting AD and/or BC dates
 

Hi Claus,
Yes, but I believe that it is a good approximation. Now I have a new "problem" to solve that is look for a specific sum of values in a column that match a specific criterium. For example: Look for the situations for which the value is 30 and type this value in an adjacent column:
5.0
18.0
12.0 30.0
11.0
6.0
1.0
11.0
24.0
11.0
6.0
6.0
12.0
6.0 30.0
5.0
12.0


Em sábado, 20 de julho de 2013 06h22min51s UTC-3, Claus Busch escreveu:
Hi Luciano,



Am Fri, 19 Jul 2013 07:17:52 -0700 (PDT) schrieb Luciano:



There was a cell format problem with my sheet.



you know that this calculation is not exactly because there have been

some calendar changes in your period?





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Subtracting AD and/or BC dates
 

Hi Luciano,

Am Sat, 20 Jul 2013 14:34:11 -0700 (PDT) schrieb Luciano:

5.0
18.0
12.0 30.0
11.0
6.0
1.0
11.0
24.0
11.0
6.0
6.0
12.0
6.0 30.0
5.0
12.0

if you want to sum A:A with criteria in B:B is 30 then try:
=SUMIF(B1:B1000,30,A1:A1000)
If you want to find all values in A:A if criteria in B:B is 30 then try:
=IFERROR(INDEX($A$1:$A$1000,SMALL(IF(B$1:B$1000=30 ,ROW($1:$1000)),ROW(A1))),"")
and enter the array formula with CTRL+Shift+Enter and copy down.


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