Stephan
Member
HELLO! Attached is XLSX Spreadsheet of 45 & 5G Internet Speeds, Photo @ Btm. There is 3 issues :
(Q1: MTH etc Formula needs adding, Q2: COND FORMAT edit MTH etc, Q3: COND FORMAT edit MIN)
QUESTION 1.
How to express HOUR:MIN Total (D5:D18), correctly as MTH:WKS:DAY:HOURS:MINS (E5:E18)?
ie not MONTH as calendar name, just the number remaining in time, if that makes any sence!
Current edit is as:
FORMATS CELLS > CUSTOM:
d "Day" : h "Hr" : m "Min"
The reason for currently excluding month, if I enter YM for MONTH it returns incorrect value of total time!
QUESTION 2.
CONDITIONAL FORMAT "ADDITIONAL" RULES.. or EDIT?
There is 4 Tiers, however Formula only works accurately if less then 1 Mth
T1: MTHs / DAYS / HR / MIN
T2: DAYS / HR / MIN
T3: HR / MIN
T4: MIN
These are to appear in a cascading applicable only #Matrix esque trickle# down of date length in CELLS E5:E18, depending on COMBO BOX VALUE E3, in other words only applicable dates to appear, like 1hr : 45 min. Instead of: 0 Mth : 0 Wk : 0 Day : 1hr : 45 min.
CONDITIONAL FORMAT: E5:E18
> FORMULA DETERMINE WHICH CELLS FORMAT:
=DAY(E5)=0
> FORMAT > NUMBER > CUSTOM:
h "Hr" : m "Min"
Currently it calculates & expresses as intended, but only if TOTAL HOURS D5:D18 do not exceed 1mth approx 720 hours+
QUESTION 3.
CONDTIONAL FORMAT show only T4 (MIN) is applicable
the next logical conditional format continuation extension from my understanding, this….
> FORMULA DETERMINE WHICH CELLS FORMAT:
=HOUR(E5)=0
> FORMAT > NUMBER > CUSTOM:
m "Min"
It did not achieve the result I was hoping for.. How unexpected.. Not sure what I'm doing wrong here as it displays 1 MIN…
See photo, examine COLUMN D for correct total hours:mins, then can see that corresponding cascading (applicable only aka Matrix trickle down Dates..) MTH/DAY/HOUR/MIN in COLUMN E is wrong, when over 1 MONTH.

CROSS POST(s): https://www.excelforum.com/excel-fo...ondition-format-display-only.html#post6016905
(Q1: MTH etc Formula needs adding, Q2: COND FORMAT edit MTH etc, Q3: COND FORMAT edit MIN)
QUESTION 1.
How to express HOUR:MIN Total (D5:D18), correctly as MTH:WKS:DAY:HOURS:MINS (E5:E18)?
ie not MONTH as calendar name, just the number remaining in time, if that makes any sence!
Current edit is as:
FORMATS CELLS > CUSTOM:
d "Day" : h "Hr" : m "Min"
The reason for currently excluding month, if I enter YM for MONTH it returns incorrect value of total time!
QUESTION 2.
CONDITIONAL FORMAT "ADDITIONAL" RULES.. or EDIT?
There is 4 Tiers, however Formula only works accurately if less then 1 Mth
T1: MTHs / DAYS / HR / MIN
T2: DAYS / HR / MIN
T3: HR / MIN
T4: MIN
These are to appear in a cascading applicable only #Matrix esque trickle# down of date length in CELLS E5:E18, depending on COMBO BOX VALUE E3, in other words only applicable dates to appear, like 1hr : 45 min. Instead of: 0 Mth : 0 Wk : 0 Day : 1hr : 45 min.
CONDITIONAL FORMAT: E5:E18
> FORMULA DETERMINE WHICH CELLS FORMAT:
=DAY(E5)=0
> FORMAT > NUMBER > CUSTOM:
h "Hr" : m "Min"
Currently it calculates & expresses as intended, but only if TOTAL HOURS D5:D18 do not exceed 1mth approx 720 hours+
QUESTION 3.
CONDTIONAL FORMAT show only T4 (MIN) is applicable
the next logical conditional format continuation extension from my understanding, this….
> FORMULA DETERMINE WHICH CELLS FORMAT:
=HOUR(E5)=0
> FORMAT > NUMBER > CUSTOM:
m "Min"
It did not achieve the result I was hoping for.. How unexpected.. Not sure what I'm doing wrong here as it displays 1 MIN…
See photo, examine COLUMN D for correct total hours:mins, then can see that corresponding cascading (applicable only aka Matrix trickle down Dates..) MTH/DAY/HOUR/MIN in COLUMN E is wrong, when over 1 MONTH.

CROSS POST(s): https://www.excelforum.com/excel-fo...ondition-format-display-only.html#post6016905
Attachments
Last edited: