walkerx   10 #13 Posted April 6, 2017 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. Share this post Link to post Share on other sites Share this content via...
Katie Event   10 #14 Posted April 6, 2017 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. Share this post Link to post Share on other sites Share this content via...
walkerx   10 #15 Posted April 6, 2017 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 Share this post Link to post Share on other sites Share this content via...
altus   534 #16 Posted April 6, 2017 (edited) 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. Edited April 6, 2017 by altus Share this post Link to post Share on other sites Share this content via...
walkerx   10 #17 Posted April 6, 2017 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 Share this post Link to post Share on other sites Share this content via...
sgtkate   10 #18 Posted April 7, 2017 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 Share this post Link to post Share on other sites Share this content via...
Katie Event   10 #19 Posted April 11, 2017 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! Share this post Link to post Share on other sites Share this content via...
iansheff   84 #20 Posted April 12, 2017 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. Share this post Link to post Share on other sites Share this content via...
altus   534 #21 Posted April 12, 2017 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. Share this post Link to post Share on other sites Share this content via...
Katie Event   10 #22 Posted April 12, 2017 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 Share this post Link to post Share on other sites Share this content via...