Adding/subtracting time and displaying hours, minutes, seconds

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NEMO - 17 Apr 2008 06:33 GMT

I'm subtracting sunset-sunrise to get total daylight time. I'm then dividing
that by 12
The formula I'm using is: =TIME(0,((H71-E71)*24*60)/12,0). Given the
following:
Sunrise:    5:09 AM
Sunset:    6:39 PM

the formula produces: 1:07:00. I want it to display the seconds. It should
look like: 1:07:30. Well, according to my long-hand calculations

I then use that in a subsequent table that looks like:

5:09 AM    6:17 AM
6:17 AM    7:25 AM
7:25 AM    8:33 AM
8:33 AM    9:41 AM
9:41 AM    10:49 AM
10:49 AM11:57 AM
11:57 AM1:05 PM
1:05 PM    2:13 PM
2:13 PM    3:21 PM
3:21 PM    4:29 PM
4:29 PM    5:37 PM
5:37 PM    6:45 PM

Where the value on the right is the sum of 1:07:00. I don't want the
calculation to round up or down.

Thanks

Reply to this Message

Tyro - 17 Apr 2008 06:47 GMT

In your formula =TIME(0,hours,0) you'll notice that the hours are 0, the
minutes are ((H71-E71)*24*60)/12 and the seconds are 0. To get seconds, you
have to input some seconds. See Help for the syntax of TIME - ie.
=TIME(Hours,minutes,seconds).

Tyro

> I'm subtracting sunset-sunrise to get total daylight time. I'm then
> dividing
[quoted text clipped - 27 lines]
>
> Thanks

Reply to this Message

Tyro - 17 Apr 2008 06:51 GMT

Correction:
In your formula =TIME(0,minutes,0) you'll notice that the hours are 0, the
minutes are ((H71-E71)*24*60)/12 and the seconds are 0. To get seconds, you
have to input some seconds. See Help for the syntax of TIME - ie.
=TIME(Hours,minutes,seconds).

> I'm subtracting sunset-sunrise to get total daylight time. I'm then
> dividing
[quoted text clipped - 27 lines]
>
> Thanks

Reply to this Message