Military Time Calculations

[ahramos]

I have Sunrise/Sunset data in the following format:

January February...

Day Rise Set Rise Set...
01 0722 1701 0710 1732
02 0722 1702 0710 1734
. . . . .
. . . . .
. . . . .
28 0714 1728 0640 1801
29 0713 1729
30 0712 1730
31 0711 1731

I need to calculate the length of time between the setting of the sun and sunrise the following day. The values ARE NOT time values but numbers. What combination of formulas can I use to do this calculation?

Basically, I'm trying to quantify the time difference of scheduling exterior lights (6:30AM Off - 7:00PM On) vs. using sensors. Thank you in advance.

 

[francesca]

Write a macro to convert your numbers to times. Then the problem becomes simple.

To convert the numbers, it will be slightly cumbersome. First convert to text, then capture the left two characters, then the right two characters in separate variables. Then reformat to time and enter the new value as left:right and you're on your way. You're very lucky in that your single digit hours are prefixed by a 0 (which suggests that the field is already formatted as text).

 

[johnwm]

If your rise and set times are in B1 and C1, use this in D1

=(TIMEVALUE(LEFT(C1,2)&":"&RIGHT(C1,2)))-(TIMEVALUE(LEFT(B1,2) & ":" & RIGHT(B1,2)))

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[IRstuff]

You'd probably want to add 24 hrs to the values in column C, since they're a day later, otherwise you'd wind up with 07:10-17:01, resulting in a negative time.

TTFN

 

[ahramos]

Thanks everyone for your help. I was able to get it after following the directions on the first response. I ended up taking the difference between the sunset and sunrise in the same day to calculate "daytime" and 24 - "daytime" as "night".
Gotta love this site.

 

[MintJulep]

The DIV, or MOD, or REMAINDER or some such function or combination thereof could be used to parse out hours and minutes without having to treat the values as text.

 

[jghrist]

[tt]
A B C
1 Day Rise Set
2 01 0722 1701
3 02 0722 1702
[/tt]
The formula =1+TRUNC(B3,-2)/2400+MOD(B3,100)/1440-TRUNC(C2,-2)/2400-MOD(C2,100)/1440 formatted as time (hh:mm) will show the time between sunrise of Day 02 and sunset of Day 01.

As long as there are no trailing spaces, Excel will automatically convert text to numbers.

 

[pmover]

ahramos,

the following website has a workbook that does sunrise/sunset calcs for any timezone. code is included.

http://williams.best.vwh.net/ftp/avsig/

workbook is: avform.xls

and the last sheet does the calcs.

good luck!
-pmover