FORMULA / CONDITIONAL FORMAT: correct HRS:MINS into cascading trickle appear MTHS:WKS:DAYS:HRS:MINS

[Stephan]

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

 

[Stephan]

also considered the following DATE DIF edits,
PROS: Can easily accomodate calc of HOURS into DESIRED DATE FORMAT
CONS: Lengthy Formula, and CONDITIONAL FORMAT to exclude BLANK DATE CRITERIA does not work with it as DATEDIF, bummer....
All in all, not the result I was looking for yer.. But I mention coz sum1 will say should tried DATE DIF 1st yer..
so... what I have done here is used ZERO (O) value as STARTER DATE, then Direct Cell ref to HRS : MINS, as already sayes at it calculates it right... but all the preluding zeros if less time then value, distracting..

DATE DIF exc CondFormat: YR:MTH:WK:DAY:HR:MIN
=DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"y")&" years, "&DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"ym")&" months, "&INT(DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"md")/7)&" weeks, "&MOD(DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"md"),7)&" days, "&TEXT(G21-S10,"h"" hours, "" m"" minutes""")

DATE DIF exc CondFormat: MTH:WK:DAY:HR:MIN
=DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"ym")&" Mth : "&INT(DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"md")/7)&" Wk : "&MOD(DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"md"),7)&" Day : "&TEXT(G21-S10,"h"" Hr : "" m"" Min""")

DATE DIF exc CondFormat: MTH:DAY:HR:MIN
=DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"ym")&" Mth : "&INT(DATEDIF(S10,G21 -(MOD(S10,1)>MOD(G21,1)),"md"))&" Day : "&TEXT(G21-S10,"h"" Hr : "" m"" Min""")

& yer.. the other area I pursued into... already is FORMAT CELLS of original designation of DATE FORMAT, however in this full inclusion, the MTH is wrongly +1, presuming it sees a value as like start of like a calendar...any value is in start of 1mth as... 1 mth is 1 etc, u know like a DATE instead of HOURS total of TIME... Excel can be a court jester sometimes can't it be..

HRS : MINS (FORMAT CELLS > CUSTOM):
yy "YR" : mm "MTH" : dd "DAY" : hh "HR" : mm "MIN"

SEE PIC PHOTO BELOW, easier to contemplate as image, probably: