Calculating Time - Adding Fifteen Minutes to Each Work Order

I deal with work orders? I want to be able to automatically add (:15) fifteen minutes to each work order. In other words, I have 399 work orders? I want to add (:15) fifteen minutes to each and every work order thereafter.

Popular White Paper On This Topic

Where are orders located? Excel workbook(s)? Each order in separate
workbook, in separate sheet inside the same workbook, or database in the
worksheet (records)?
In Excel it can be accomplished using the OnTime event.

Convert 15 minutes to time = 15/(24 *60) representing the equivalent fractional portion of a day. Put that in a cell, say A1. Then add the resulting constant to your time cell, say A2, which should be formatted as h:mm AM/PM. Format cell A3 as h:mm AM/PM and A3's formula should be A1+A2. If you want to do dynamically using VBA that would be a change event which is more involved.

Are you trying to add fifteen minutes to the elapsed time recorded for a work order, or to some recorded point in time such as the start time, or the end time?

Are you trying to make each work order have a time 15 minutes later than the previous work order?

As John stated, Excel represents time (and dates) as a floating point number. The integer portion of the number is the number of days since the base date, while the fraction portion of the number is the fraction of a day - in other words, the time of day. A day starts at midnight; noon, being halfway through the day, is 0.5; 6:00 AM, being one quarter of the way through the day, is 0.25. One second after midnight is 1/(60*60*24) or 1/86,400 of a day or 0.00001157407407407407...

Therefore, to add 15 minutes to an existing date/time value, you add (15*60)/(24*60*60) or 1/96 or 0.010416... to the numeric value.

This is really a completely different question than you originally asked.

To restate your latest post, you want to calculate a single value which is the product of the number of completed work orders and a period of time per work order.

Because you are looking for a result in hours, you need to express the time per work order in hours. Fifteen minutes is 0.25 hours.

Columns, which consist of many cells in a vertical sequence, are designated by letters, not letters and numbers. "G9" designates a single cell in a column.

If the cell G9 contains the number of completed work orders, then the cell J10 should contain the formula:

=G9 * 0.25

On the other hand, if you really meant that column G contains indications that work orders are completed (for example, the cell in column G is blank for a work order in progress, but contains the time that the work order is finished if it is completed), then you need to use a function that will count the number of completed work orders. In that case, the formula in cell J10 might contain:

=COUNTIF(G:G, <>"")

The designation "G:G" selects the entire column. The "criteria" <>"" counts the cells in the designated range that do not equal the empty string (that is, the cells that are not blank).

OK. I divided your result (50.75) by .010417 (the fractional equivalent of 15 minutes) and got 4872. I then divided 4872 by 203 and got 24. So I assume each work order is the equivalent of 24 hours? Instead of using :15 in your formula replace it with its fractional equivalent of 15/(24x60).

I may still be missing something. Hopefully I've given you a clue that will help.
John Bigness

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.