Extact minutes from text field?

[Lenchik]

Hi,

I have data in the follwing format in one cell: 0d 0h 13m and I need
to display only minutes and then if amout of minutes greater than 15,
color in red.

I tried:

TEXT('Historic Data'!H11,"h"" hr"" m"" min""")

and

TIME(HOUR('MyTab'!H11), MINUTE('MyTab'!H11), 0)


they will both display hr and min.

If I try:

TEXT('Historic Data'!H11," m"" min""") instead of 13 min, I get 1 min.

I'd really appreciate some help...

Thanks a ton,

Elena

 

[Lenchik]

This works:

=IF('MyTab'!H11>TIME(23,59,59),TEXT('MyTab'!H11,"d""d"" h""h""
m""m"""),IF('MyTab'!H11>TIME(0,59,59),TEXT('MyTab'!H11,"h"" hr"" m""
min"""),MINUTE('MyTab'!H11)&" min"))

Struggling with conditional formatting though....

 

[daddylonglegs]

Here's a shorter formula


=IF('MyTab'!H11>=1,INT('MyTab'!H11)&"d
","")&IF(HOUR('MyTab'!H11)>0,HOUR('MyTab'!H11)&" hr
","")&MINUTE('MyTab'!H11)&" min"

For conditional formatting it's more compilcated to look at the result
generated from that formula because it's a text string so it might be prudent
to reference the original value in MyTab!H11. I presume that's a different
worksheet from where your formula is located so try naming H11, something
like Timecell and the use that name in conditional formatting, i.e. use
"formula is" with formula

=Timecell>"0:15"+0

format red

 

[Rick Rothstein \(MVP - VB\)]

I have data in the follwing format in one cell: 0d 0h 13m and I need

to display only minutes and then if amout of minutes greater than 15,
color in red.

I was a little confused by your posting... you showed two different
worksheet names referencing the same cell value and it unclear where you are
at when trying to retrieve the minutes. On top of that, the various formulas
you tried seemed to be looking at things other than minutes. So, I just
address the bare essentials and you can add whatever sheet references you
need.

If your data is a **real Excel time value** simply formatted to display as
you indicated, then you can get the number of minutes using the MINUTE
function...

=MINUTE(H11)

You actually used this function in one of your TEXT function attempts, so
you apparently already know the above. This leads me to believe your data is
simply a text string and not a real Excel time value. If that is the case,
then you can get the minutes value, as text, like this...

=TRIM(LEFT(RIGHT(H11,3),2))

If, however, you want the value as a number, you can to that like this...

=--LEFT(RIGHT(H11,3),2)

using the double unary to convert the text string to a numeric value (the
TRIM function call is not necessary for this implementation as the possible
leading space for a single digit minute do not interfere with the
conversion).

Does this address what you were originally asking about?

Rick

 

[Lenchik]

Thanks a million! It worked flawlessly...

Here's a shorter formula

=IF('MyTab'!H11>=1,INT('MyTab'!H11)&"d
","")&IF(HOUR('MyTab'!H11)>0,HOUR('MyTab'!H11)&" hr
","")&MINUTE('MyTab'!H11)&" min"

For conditional formatting it's more compilcated to look at the result
generated from that formula because it's a text string so it might be prudent
to reference the original value in MyTab!H11. I presume that's a different
worksheet from where your formula is located so try naming H11, something
like Timecell and the use that name in conditional formatting, i.e. use
"formula is" with formula

=Timecell>"0:15"+0

format red