I use formula to display 3 hours 7 mins as 3,07. However for some reason the same formula does not work on Win7 Office 2013 English version it says #VALUE. Why so and how to repair it? Maybe other formula to suite for both?
Windows 10 Office 365 European version
=CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour"; R96)); "0 hours "; "")&R96;"s";"");" min";"");" hour ";",");0,1)
Windows 7 Office 2013 English version
=CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour", P96)), "0 hours ", "")&P96,"s","")," min","")," hour ",","),0.1)
3,7 is not a number, thus when the formula reaches this point:
=CEILING("3,7",0.1)
You will get the #VALUE error. You can use the following instead to make it work (notice the change):
=CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour", P96)), "0 hours ", "")&P96,"s","")," min","")," hour ","."),0.1)
^
Which results in 3.7. If you need it to be a comma, you will need a final SUBSTITUTE:
=SUBSTITUTE(CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour", P96)), "0 hours ", "")&P96,"s","")," min","")," hour ","."),0.1), ".", ",")
This looks like an issue caused by the Decimal separator differing between different countries: Some write "one thousand and a half" as "1,000.5", others write it as "1.000,5"
So, you need to ensure that your "," knows when it should be "." instead. The simplest way that I can think of for that is MID(1/2,2,1) - that is, take the middle character from a half. Depending on your settings, this will be 0.5 (result: ".") or 0,5 (result: ",")
(The 0.1 at the end should change itself automatically between versions, since it is an actual number - if not, then change it to 1/10 for a similar result)
=CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour"; R96)); "0 hours "; "")&R96;"s";"");" min";"");" hour ";MID(1/2,2,1));1/10)
Also to consider: Your current system will write "2 hours 1 minute" as "2,1" (or "2.1" depending on the settings) - but, it will also write "2 hours 10 minutes" as "2,1". Your requirements may need to be reviewed
