Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Formatting: Can I set up a condition?
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 dont 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - Getting pass the 3 condition limit | Excel Discussion (Misc queries) | |||
Why won't excel apply my custom cell formatting? | Setting up and Configuration of Excel | |||
Custom Formatting | Excel Worksheet Functions | |||
Formatting custom currency quirk | Excel Discussion (Misc queries) | |||
enhanced conditional formatting | Excel Discussion (Misc queries) |