Military Time Calculations
Military Time Calculations
(OP)
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.
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.





RE: Military Time Calculations
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).
RE: Military Time Calculations
CODE
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Military Time Calculations
TTFN
RE: Military Time Calculations
Gotta love this site.
RE: Military Time Calculations
RE: Military Time Calculations
A B C
1 Day Rise Set
2 01 0722 1701
3 02 0722 1702
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.
RE: Military Time Calculations
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