Extract minute value

BTW, I also tried "=MINUTE([@[Due Time]])" but that didn't work either and returned all zeros.

Thanks all.
I used this and it worked like a charm: =(TIME(,MINUTE([@[Due Time]]),))*1440

Originally Posted by joeu2004

I cannot imagine why that would work, but not simply =MINUTE([@[Due Time]]). They should return the same value [1].

For my edification, can someone else confirm that MINUTE([@[Due Time]]) does not work(!)? FlameRetired?

(Recall that I cannot or do not how to use table object references. Sigh.)


-----
[1] Actually, TIME(...)*1440 is at risk of returning a non-integer. It should be rounded explicitly. But I confirmed that TIME(...)*1440 does indeed return integers for all minutes 1 through 59, when used in this manner.

I included the test file in my first post. Did you make it work without using the fractional multiplier (1440)?

Originally Posted by FlameRetired

@ mariomp

There seems to be some confusion regarding how Excel deals with dates and times and formatting.

If you aren't aware of it dates are integers. Days are numbered starting at 1 (1/1/1900). Today 11/22/2017 is day 43061. The formatting is for human eyes.

Time however is a decimal "fraction" of a day. With 1440 minutes in a day 4 minutes = 4/1440 or about 0.00277777777777778. Once again the formatting is for human eyes. Excel "sees" that decimal.

If you read my previous post to joeu2004 (#10) it may help answer.

A lot depends upon how you wish to use the results of the formula. Do you want minutes as integers or as fractions of a day that they are?

Thanks for additional info.
As stated in post #6, I got it working and working well.
I marked the thread as solved.
Joe had some questions afterwards, and I was tying to be helpful.

Originally Posted by mariomp

I used this and it worked like a charm: =(TIME(,MINUTE([@[Due Time]]),))*1440

Originally Posted by joeu2004

I cannot imagine why that would work, but not simply =MINUTE([@[Due Time]]). They should return the same value

Originally Posted by mariomp

Did you make it work without using the fractional multiplier (1440)?

You don't seem to understand.

If =TIME(0,MINUTE([@[Due Time]]),0)*1440 works for you, so should simply =MINUTE([@[Due Time]]). Both should return the integer 4 when the time is 19:04, for example.

When I tried the latter the first time, I got an inexplicable syntax errors. But that is simply because I do not know how to work with table object references.

When I tried it again, replacing your formula in column B, both formulas do indeed work if I also change the cell format.

That is, initially Excel displays 00:00 for both formulas. That is not "all zeros"; it is simply 00:00 time of day.

When I format as General, I do indeed see the integer 4.

So you should use simply =MINUTE([@[Due Time]]).

Originally Posted by joeu2004

You don't seem to understand.

If =TIME(0,MINUTE([@[Due Time]]),0)*1440 works for you, so should simply =MINUTE([@[Due Time]]). Both should return the integer 4 when the time is 19:04, for example.

When I tried the latter the first time, I got an inexplicable syntax errors. But that is simply because I do not know how to work with table object references.

When I tried it again, replacing your formula in column B, both formulas do indeed work if I also change the cell format.

That is, initially Excel displays 00:00 for both formulas. That is not "all zeros"; it is simply 00:00 time of day.

When I format as General, I do indeed see the integer 4.

So you should use simply =MINUTE([@[Due Time]]).

Got it, thanks. That simplifies things, and I'm always about KISS