How to use the IF function when part of the data is a time?

New Contributor

I am trying to build a spreadsheet that builds a cost each day from some power usage of a piece of equipment. I therefore have two columns of when the equipment starts and when it stops. It only runs once in any one day and the columns are formatted with the time function (hour:min; so 7:52, 20:36). To work out the cost the power is billed across five different rates across a 24 period. When I try to use the IF function to work out how much power should be charged at say the peak morning rate I get an #error!

 

I've tried using the function on other data that is not in the time format and it works fine. What am I doing wrong? 

 

Thanks

 

3 Replies

@GaryGiles 

The columns are formatted to display the time, but what do you see in the formula bar when you select one of the cells? Just the time, or the date plus the time?

Just the time in the formula bar - formatted as 00:00:00.

@GaryGiles 

See the attached demo workbook. Column G contains formulas that should work in all versions of Excel; column H contains formulas that are more readable but work only in Office 2021/Microsoft 365.