×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Military Time Calculations

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.

RE: Military Time Calculations

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).

RE: Military Time Calculations

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

CODE

=(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: www.essexsteam.co.uk

RE: Military Time Calculations

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



RE: Military Time Calculations

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

RE: Military Time Calculations

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.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources