add and subtract time

**rookie37** · 06-01-2009, 03:09 AM

How do I add and subtract hours minutes and seconds?

I’m writing a program to find when the sun is directly overhead. I’m doing this so that I can find the true north/south line. In the example on my program, at the town of Farmington, a plumb bob will cast a shadow on the north/south line at 11:51:39 on June 16

Can somebody help me with the holes in my program? I’m almost there
(EoT sheet 2)

**arthurbr** · 06-01-2009, 03:13 AM

Hi,
lots of info can be find at this site or this one

**DonkeyOte** · 06-01-2009, 03:14 AM

Not entirely clear to me where you want the calculation... perhaps you want:

="12:00"+((B33-C12)/86400)

Above based on your expected output of 11:51:39

Time in XL is Decimal - ie Noon is 0.5 ... in your example B33 & C12 are Seconds but stored as Integers - to convert to true XL Time equivalent we divide the value by 86400 (24*60*60).

**rookie37** · 06-01-2009, 07:20 PM

How do I add and subtract time?

I'm writing a program so that I can find the true north/south line. (not magnetic)

On sheet 2 of my program I have;

The town of Farmington is on Eastern Standard time. The standard meridian is 75 deg
Farmington is 72 deg 49 min or 2 deg 11 min difference in location. The sun will be directly overhead in Farmington 8 min 44 seconds sooner than it will be over the standard meridian. (or 4 minutes of time for each degree) On jun 16 the sun will be 23 seconds slow

The formula is S.T. = 12:00:00 - 8 min 44 sec + 23 sec
S.T. = 11:51:39

At that time a plumb bob will cast a shadow on the true north/south line

**shg** · 06-01-2009, 07:32 PM

You'd make your life a lot simpler if you kept lat/lon in decimal degrees:

="12:00" + ( 4 * ( lon - refLon) - (C7 + D7/60) ) / 1440

**rookie37** · 06-02-2009, 12:46 AM

shg

I know very little about excel. I don't know how to put in the formula you gave me

**DonkeyOte** · 06-02-2009, 03:30 AM

This is a duplicate post and as such does not comply with Rule 5 of our forum rules.

Normally this thread would now be closed, however, on this occasion given shg has provided an alternative approach I will merge the two threads.

In short don't post dupes - bump the original if you don't have the required solution and explain what does not work and why.

**shg** · 06-02-2009, 08:01 AM

I second DO's request.

If the EoT calculates time as standard Excel dates (whole numbers = days since 00 Jan 1900, fractions = time), you can simplify as

Please Login or Register  to view this content.

The only inconvenient part is that Excel won't display negative times as, for example, -0:00:23, so you have to show it in decimal format as above.

**rookie37** · 06-02-2009, 07:31 PM

Originally Posted by DonkeyOte

This is a duplicate post and as such does not comply with Rule 5 of our forum rules.

Normally this thread would now be closed, however, on this occasion given shg has provided an alternative approach I will merge the two threads.

In short don't post dupes - bump the original if you don't have the required solution and explain what does not work and why.

I didn't mean to duplicate. When I put in the first post, I couldn't find it. I thought it didn't go through. This is why I put it in again

**rookie37** · 06-06-2009, 03:46 AM

Thank you all for your help. I solved it. (probably very unorthodox way by programming standards)

I kept the time in number format. I then separated them