Custom Formatting: Can I set up a condition?

Here's what I have:

Column A = Start time
Column B = Nominal Sample Time (i.e., 5 m, or 5 min from start time)
Column C = Relative Sample Time (Actual time - start time)
Column D = Actual Sample Time

Wanted: If(time in column C is less than 60 minutes, I need format to
be ‘x m’ where x = # of minutes)

If(Itime in column C is greater than 60 minutes, I need
format to be ‘y h x m’ where y = # hours and x = # minutes

BUT, I don’t want any 0 hours or 0 minutes (e.g., 0 h 4 m
must = 4 m. Or 5 h 0 m must = just 5 h)

I've tried the custom formatting of (h "h" m "m"), which works for the
whole thing (e.g., 5 h 42 m), but it also yields 0 h 4 m and 5 h 0 m.
I need the later two to be just '4 m' and '5 h'.

I also need it to show 24 h (and greater if there has been more than
24 hours passed).

Finally, the Actual Sample Time may be before the Start time, which
may result in a negative time. How can I get it to display a negative
time.

Thanks for any help, and please let me know if you need some
clarification to help me.

Frank

Frank, what is the format of your times in the sheet as you cannot have a
negative time and what does a time of over 23:59 look like.

This will help some.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Phrank" wrote:

Here's what I have:

Column A = Start time
Column B = Nominal Sample Time (i.e., 5 m, or 5 min from start time)
Column C = Relative Sample Time (Actual time - start time)
Column D = Actual Sample Time

Wanted: If(time in column C is less than 60 minutes, I need format to
be ‘x m’ where x = # of minutes)

If(Itime in column C is greater than 60 minutes, I need
format to be ‘y h x m’ where y = # hours and x = # minutes

BUT, I don’t want any 0 hours or 0 minutes (e.g., 0 h 4 m
must = 4 m. Or 5 h 0 m must = just 5 h)

I've tried the custom formatting of (h "h" m "m"), which works for the
whole thing (e.g., 5 h 42 m), but it also yields 0 h 4 m and 5 h 0 m.
I need the later two to be just '4 m' and '5 h'.

I also need it to show 24 h (and greater if there has been more than
24 hours passed).

Finally, the Actual Sample Time may be before the Start time, which
may result in a negative time. How can I get it to display a negative
time.

Thanks for any help, and please let me know if you need some
clarification to help me.

Frank