Replacing one value with another

[lukerees83]

In one of my spreadsheet columns I have a bunch of zeros that represent the time value of 12 midnight. However, one of the formula I am using doesn't work if the value is zero but would work if the value was 24. (I don't want to change to time-format cells if I can avoid it.)

So how would I write a formula that says "if any cell in column H shows 0 replace this with 24".

Many thanks.

 

[shg]

Maybe find and replace 0 with 24.

Why doesn't the formula work with 0?

 

[lukerees83]

The original data I'm working from is badly formatted i.e. the cells are not in time format and also the hours and the minutes are in separate cells. In order to calculate time difference therefore I've multiplied the number of hours by 60 and added it to the minutes column to make a total number of minutes. That way I can have an exact number of minutes difference between 2 times with a simple subtraction formula. However if midnight is registered as 0 and I have to calculate, say, the difference in minutes between a minute past midnight and 11.59 it will produce a massive negative number unless midnight is represented as 24. I didn't design the original and wish I could slap whoever did, as I'm no expert at excel myself and this is the only way I have found to rectify it.

Can you ask excel to do a find and replace in 1 column only? As I don't want to convert all the zeros in my spreadsheet.

 

[Mike Blackman]

Yep,

If you select a range of cells (more than one) then the Find/Replace only works on that range.