Astro Calculation

[satishkumar123]

Hi,


For Astro Calculation, I looking for a excel formula to add the 2 dates with Hr and Min.


Each components (Year,Month,Day,Hr,Min) are in individual cell.


Take the below data....

[pre]

Year = A1,A2
Month=B1,B2
Day=C1,C2
Hr=D1,D2
Min=E1,E2
Year Mon Day Hr Min
1978 08 09 16 51
0000 04 09 14 24
--------------------

[/pre]
----------------------

After adding the above dates, I should get final added values as "1989-12-19-7-15."


But I am getting "1989-12-18-7-15." by using the below formula.

=DATE(SUM(A1:A2),SUM(B1:B2)+INT(SUM(C1:C2)/30),MOD(SUM(C1,C2),30)) + TIME(D1+D2,E1+E2,0)


In the Time factor, if the hour is more than 24, the quotient part should add to the day component.

Hour ==> 16+14 = 30 hrs. So 1 day & 6 hour.

Min==> 51+24 = 75 min. 1 hr & 15 min.

The Hr becomes as 7 ( 6 hr + 1hr)

The Min is 15 min.

The day should be added with one (ie., 1 day [Hour ==> 16+14 = 30 hrs. So 1 day & 6 hour.])


Note- By default a month hold 30 days only.


Thanks

Sat.

 

[Hui]

Sat


try:

=DATE(A1+A2,B1+B2,C1+C2)+TIME(D1+D2,E1+E2,0)

or

=DATE(A1,B1,C1)+TIME(D1,E1,0)+DATE(A2,B2,C2)+TIME(D2,E2,0)


Excel takes care of how many Mins in Hrs, Hrs in Days, Days in Months, Months in Years etc

 

[NARAYANK991]

Hi Satish ,


I don't think it is so easy , since the summing has to start from the minutes , and progress towards the years ; if the minutes exceed 59 , then the hours component has to increase by 1 ; if the hours exceed 24 , the days component has to increase by 1 , if the days exceed 30 , the months component has to increase by 1 , and if the months component exceeds 12 , the years component has to increase by 1 ; in each case , when there is a carry over , the lower component has to decrease accordingly e.g. if the minutes equals 77 , then after the hours component has increased by 1 , the minutes component now becomes 17.


And I am not able to understand how you get 1989 ; the correct answer should be :


1979 0 19 7 15


If we convert the first set of numbers into one number , we get :


=A1+(B1+(C1+(D1+E1/60)/24)/30)/12


which evaluates to 1978.69361689815


Do the same for the second set of numbers , to get 0.36


Adding the two , we now go through the reverse process to end up with the result I mentioned above.


Narayan

 

[Sajan]

Hi Narayan,

I haven't played around with date/time functions much... but here is a slightly different interpretation:


For the source data:

[pre]

Year	Mon	Day	Hr	Min
1978	8	9	16	51
0	4	9	14	24
I calculated the final result as follows (with the base date in cell H1)

Base	        8/9/1978 16:51	         =DATE(A2,B2,C2)+TIME(D2,E2,0)
Add time 14:24	8/10/1978 07:15	         =H1+TIME(D3,E3,0)
Add days 9	8/19/1978 07:15	         =H2+C3
Add months 4	12/17/1978 07:15	 =H3+B3*30
Add years 0	12/17/1978 07:15	 =H4+A3*12*30
All together	12/17/1978 07:15	 =DATE(A2,B2,C2)+TIME(D2,E2,0) + TIME(D3,E3,0) + C3 + B3*30 + A3*12*30

[/pre]
Excel will add the correct number of days in a month, even though we assumed that there are exactly 30 days in a month. As a result, if we add 30 days + 9 days, the result will vary depending on the start/base date.


Regards,

Sajan.

 

[NARAYANK991]

Hi Sajan ,


Sorry but I made a mistake in arriving at 1979 by adding 8 and 4 months ; since they do not exceed 12 , there is no carryover ; my earlier posted result should be :


1978 12 19 7 15


which is the same as yours , except for the days ; since Satish has said that all months have 30 days , I think adding 9 and 9 and 1 gives 19 instead of 17. Let us leave it to Satish to confirm. When he mentions that all months have 30 days , my interpretation is that if you are on a particular date in any month , adding 'x' months means you reach the same date , not really adding 30 * 'x' days.


I have outlined the process which should be followed to arrive at the final result ; is this right or wrong ? Only Satish can confirm.


Narayan

 

[SirJB7]

Hi, satishkumar123!


Try this formula:

=FECHA(A2+A3;B2+B3;C2+C3)+D2/24+E2/24/60+D3/24+E3/24/60 -----> in english: =DATE(A2+A3,B2+B3,C2+C3)+D2/24+E2/24/60+D3/24+E3/24/60


It'll retrieve 19/12/1978 07:15 which I think is the correct day for your posted data.


Regards!


@NARAYANK991

Hi!

I assume a typo error and you meant the same date, 12 instead of 0.

Regards!


EDITED: Didn't update the browser, you've fixed it yet.


@Sajan

Hi!

Same for you, 19 instead of 17.

Regards!

 

[Sajan]

Hi Narayan,

Agreed. Satish now has his choice!


I have no idea what "astro calculations" are. (A Google search showed stuff about Astrology. Not sure if that was what Satish had in mind.)


The perspective I took was that of task finish dates, and estimates for additional work. In that context, if someone said that they would be done on July 1 at 2pm, and then wanted an additional 3 months, 4 days and 2 hours to complete the work, I would want the actual calendar dates to be used to arrive at the new finish date/time.


Isn't it fun that based on different assumptions we can arrive at different results?!


Hi SirJB7,

Now you know why I got 19 instead of 17.


Regards,

Sajan.

 

[SirJB7]

@Sajan

Hi!

Now I see, you considered 30 days months so August and October weren't counted as their actual 31.

Regards!