Computers & Internet » Software » Excel Time calculations help?

# Excel Time calculations help?

Hi, i am working with excel for some time calculations, what i need is to have times at which some machines will be operating, i have start time, running time, and the time when the machine will be ready for next run, and according to the time needed for the machines to run, i have a table for a week of running the machines, the problem is that i found out that some of the times when the machine is supposed to start working is at 5 am for example, and that's out of the work hours, so how can i add to the cells some kind of contraint that will give me results only between 6:30AM and 1:30 AM of the next day, HELP PLZ!

• you may desire to have handed the precision of Excel - it probably can no longer characterize a quantity that small. YOur 2 suitable bets are: one million. artwork in logs and exhibit your very end result as a decimal (base 10) quantity despite if that's no longer so small. 2. shop music of powers of ten your self (if accessible) so excel is purely working with floating element numbers on the order of 10 in magnitude. sort like we previous adult men did back interior the slide rule days.
0
0
• Hi LuiS,

Time calculations can be very tricky. I don't know the layout of your spreadsheet, but I came up with something that might get you on the right track. If you would like more assistance beyond what I show below, you can email your file to me so I can take a look at it. But here goes....

Since I don't know your spreadsheet, I am going to assume that in your formulas you are using the natural time and date numbers that excel gives for dates.

Each date is represented by a 5 digit number. For example, 39505 = Feb 27, 2008; 39506 = Feb 28, 2008; etc.
The decimal place after the main number represents the time of day. Since there are 1440 minutes in a day, 1:30 a.m. (or 90 minutes past midnight) is represented by the formula 90/1440 or 0.0625. So 1:30 am on Feb 27 would be represented as 39505.0625.

Of course, not all times are as "clean" as 1:30 am. 90 minute time intervals are the cleanest, but most other times require many more digits to the right of the decimal place to be "dead" accurate.

For example, 6:30 a.m. is 390 minutes past midnight, which is represented in an exact formula as 390/1440 and would be represented as a decimal equivalent as 0.270833. Of course, the more 3's you add at the end, the more accurate version of 6:30 a.m. you actualy get.

When comparing for a time, it is best to use the mathematical equivalent of "min / totmin" where:
min = minutes past midnight equivalent of the time you want.
totmin = total minutes in a 24 hour day (1440). Doing so gives you the most accurate representation of the time you want to check for.

By using the "MOD" function, you can look at a date & time number (such as the 39505.0625 mentioned above) and be able to "strip away" the number before the decimal place and evaluate only the part that comes after the decimal place, which is the time of day.

Again I don't know the layout of your spreadsheet or how you are displaying things, but for this example, let's say you have a time (and / or a date & time) displayed in cell A1. If you put the formula that I show below in Cell B1, it will tell you if it is "okay" to start the machines or if it is "too late", based on the 5 hour period between 1:30 am to 6:30 am (roughly) being too late, and the rest of the time being okay. It may not be perfect to the exact second but again it may help you get close to where you can tweak it a bit.

The formula is:

=IF ( AND ( MOD (A1 , 1 ) > ( 90 / 1440 ) , MOD ( A1,1 ) <( 389.99 / 1440 ) ) , "Too Late" , "Okay" )

Notice that in the above formula I used 389.99 / 1440 as a representation of "just before 6:30 A.M." (390 minutes) because at 6:30 am it is the start of the shift. You could just put in 390 / 1440 if you don't want to split hairs that closely.

This was long. Hope it helps.

E-mail me at dave@share2help.com if you need any clarifications on this.

Dave
0
0