Taking Time of Day into Account in a Formula

Vern runs a small job shop, and they often get people who come in late in the day and want their work done by the end of the day. The shop normally charges these people a 20% rush fee. In pricing a job in a worksheet, Vern would like to have a line that calculates the rush fee, but only if the current time is later than 3:00 pm. If it is later, then rush fee would be equal to 20% of the value in cell E15. Vern wonders if a formula can take the time of day into account in this way.

There are many different formulas that could be used for this purpose. Here, for example, is a very easy one:

=IF(NOW()>TODAY()+TIME(15,0,0),0.2*E15,0)

This works because the NOW function returns the date and time, so if the current time is greater than 3:00 pm, then 20% of what is in cell E15 is returned, otherwise 0 is returned.

There is a potential problem with this formula, however—it will recalculate every time you make a change in the worksheet. Thus, if a customer comes in at 2:45 pm and you spend time with that customer talking about the job (or the weather) that puts the time beyond 3:00 pm, the rush fee would not be included. However, it would be included if you make a change in the worksheet that causes a recalculation.

To get around this potential problem, you may want to put a job time in a different cell. For instance, you might use cell B2 for this purpose. Select that cell, then press Ctrl+Shift+;. Excel puts the current time into the cell. You can then use this formula to calculate the rush fee:

=IF(B2>TIME(15,0,0),0.2*E15,0)

You are, in this iteration, comparing the job time to the 3:00 pm cutoff; it is this time that controls whether the rush fee applies or not. (Now you can talk about the job or weather as much as you want and the job total won't change based on the time.) In order to start a new job calculation, you simply need to remember to update cell B2 with a new time as part of your data entry.

To further refine the formula, you may want to put the rush job cutoff time into its own cell (B3) and the rush job percentage into its own cell (B4). The formula would now look like this:

=IF(B2>B3,B4*E15,0)

Putting these values in their own cell provides an easy way to adjust them without needing to edit the actual formula.

Speaking of the formula, remember that I mentioned there were many ways that such a formula could be written. Regardless, the same considerations apply with those as I've discussed in relation to this particular formula.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11030) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.