Adding/subtracting time and displaying hours, minutes, seconds

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

Tyro
Thu Apr 17 00:47:05 CDT 2008

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

"NEMO" <NEMO@discussions.microsoft.com> wrote in message
news:C4FD1250-6C9A-4363-AAC6-74C86081384A@microsoft.com...
> 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
>
>

Tyro
Thu Apr 17 00:51:37 CDT 2008

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).
"NEMO" <NEMO@discussions.microsoft.com> wrote in message
news:C4FD1250-6C9A-4363-AAC6-74C86081384A@microsoft.com...
> 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
>
>