Excel query - formula for setting a maximum value

That will attempt to display the smallest value out of the value in B3, the value in C3 or the text string 17:00 - the latter doesn't have a numerical value, so the formula won't work, surely?

Nope, it will add the time together and if you subtract it will subtract the time

A lot of people under estimate excel and what it can do. You can add/subtract time and dates with simple operands

if you have excel, give it a go and you'll be surprised with the results.

https://postimg.org/image/8r3fompfr/

I get an Error in LibreOffice, which has the same function library.

The MS Excel help page says:

I don't see how it can compare the value of B£+C3 to 17:00 - the speech marks you've put around it mean Excel sees it as a piece of text.

https://postimg.org/image/8r3fompfr/

 

I get an Error in LibreOffice, which has the same function library.

 

The MS Excel help page says:

 

MIN(number1, [number2], ...)

 

The MIN function syntax has the following arguments:

 

Number1, number2, ... Number1 is optional, subsequent numbers are optional. 1 to 255 numbers for which you want to find the minimum value.

 

I don't see how it can compare the value of B£+C3 to 17:00 - the speech marks you've put around it mean Excel sees it as a piece of text.

LibreOffice/OpenOffice are limited and do not offer the same functionality as Excel.

As I stated, use Excel

just done a google search which also shows that this can be done

goo.gl/UrFeRP

Nope, it will add the time together and if you subtract it will subtract the time

 

A lot of people under estimate excel and what it can do. You can add/subtract time and dates with simple operands

 

if you have excel, give it a go and you'll be surprised with the results.

Excel and LibreOffice both store dates/times as numbers (1 day is 1 so 12 hours is 0.5). Whether they get displayed as days/times or as numbers is a formatting issue. Calculations on them operate on numbers.

Your formula is trying to find the minimum of a number and a string. As Katie says, LibreOffice reports an error - I strongly suspect Excel does too. You'd need to convert the time string to a number for it to work.

e.g. =MIN(B3+C3,TIMEVALUE("17:00"))

It may be that Excel notices that "17:00" contains a time in this circumstance and LibreOffice doesn't - they both handle ="15:32" - NOW() correctly.

excel does not report an error

http://imgur.com/a/mIgvB

if anyone uses excel would they like to try the formula i have posted

or you could actually try excel online as microsoft provide basic usage of the app for free online

excel does not report an error

 

http://imgur.com/a/mIgvB

 

if anyone uses excel would they like to try the formula i have posted

 

or you could actually try excel online as microsoft provide basic usage of the app for free online

Yes, it works fine in Excel. It also correctly calcs the hours too, so if you write the query like:

=MIN(B3+C3,"27:00")

And have these values which add up to more than 24 hours:

B3 = 13:00

C3= 12:00

It give the correct response of 01:00 and not 25:00

I don't use Excel as it won't run on my operating system. I stand corrected as to the way the function works in Excel though - as altus says, eveiddently MS have written Excel so it recognises "17:00" as a time, which is useful to know. I haven't used Excel since about 2007, and it certainly didn't used to do so!

As for adding 12:00 to 13:00 and getting the answer 01:00 - I guess whether or not that is useful depends on whether you want the answer 25 hours or 1am!

Is it not something like equals or less than 15 that goes in the formula? I once did something similar years ago but cannot remember off hand apart from what I put above.

As for adding 12:00 to 13:00 and getting the answer 01:00 - I guess whether or not that is useful depends on whether you want the answer 25 hours or 1am!

You have to remember that both programs don't have different data types for dates and times - they use a single date/time type. It's a number with days being represented as the whole number part and times as the fraction. Displaying 24 or more hours as hours rather than day plus time is a formatting issue - you just have to choose an appropriate cell formatting option.

---------- Post added 12-04-2017 at 21:02 ----------

Is it not something like equals or less than 15 that goes in the formula? I once did something similar years ago but cannot remember off hand apart from what I put above.

That's what Katie put in post #3. MIN() is just an easier way of doing it that also makes it clear what you are intending to do.

You have to remember that both programs don't have different data types for dates and times - they use a single date/time type. It's a number with days being represented as the whole number part and times as the fraction....

I haven't forgotten since post 7