Test for time vs. decimal input

I am preparing a workbook that requires the user to input time values. I don't want to restrict the user to using just one format, they should be able to enter values as hh:mm:ss or as decimal values. The idea is to automatically detect values formatted as time (which Excel does automatically if entered in a hh:mm:ss format) and convert them to decimal form. Since decimal form is the goal format, it should not be touched. There are no addition or subtraction operations issues to deal with afterward, they only get ordered and used in calculating a function of time. Please note that I want to avoid using VBA code.

I have found the following resource that gets me close to the desired result. Since I don't have many posts, you will have to use your intelligence to decipher the URL:
excelribbon [dot] tips [dot] net/T009699_Checking_for_Time_Input

That article offers two approaches. In the first approach, it uses CELL with "format" for info_type. The returned value should lie in the range D6 to D9, the usual values for time formats.

Formula:

Please Login or Register  to view this content.

The second approach converts the value using TEXT and a format code of "hh:mm:ss", converting back using TIMEVALUE and then checking whether the result is the same as the original cell.

Formula:

Please Login or Register  to view this content.

Both of these give the same results for non-zero values, but there is an issue. The methods do not recognize time formats when the time is greater than 24 hours. As the article states, "The function works fine as long as [the source] cell contains only a time. If the cell contains both a date and time, then the function always returns 'Not a Time Entry.'" When a time is entered that is greater than 24 hours (24:00:00 or more), Excel apparently converts it to date-time format, i.e., the number format is changed to [h]:mm:ss and a date is added when viewed in the formula bar (e.g., 58:49:02 is represented in the formula bar as 1900-01-02 10:49:02). I am looking either for a way to modify the test so that it can identify this case as well, or for an alternative test method. So feel free to ignore my comments here and swoop in with a completely different solution.

I have played around with the formulas in the attached workbook, which demonstrates the issue. My only success has been a somewhat better understanding. First for the formula above using CELL and "format". Take B2 as the original cell, and use "TE" for "Time entry" or "Not TE" for "Not a time entry." If I enter a decimal value, the value returned for CELL("format",B2) is G. If I enter a time, such as 6:04 or 6:04:00, Excel returns D9 and D8, respectively. If I enter 58:49:02 as above, it again returns G for general. The result of the test is therefore wrong (returns "Not TE", although for my purposes it should be "TE").

For the second formula using TEXT and TIMEVALUE, I tried to understand how Excel interprets these, and if they could be modified. When using TEXT(B2,"hh:mm:ss"), Excel expresses integers as zero, time values < 24h as time values, time values >= 24h as the remainder of the time value after removing the day value, and for decimals I believe it interprets them as the decimal format underlying the date-time system. Decimals are not a problem, since they still get detected as "Not TE," so an accurate interpretation is not needed. The real problem is time values >= 24h.

I tried using the formula with "[h]:mm:ss" instead of "hh:mm:ss", that is:

Formula:

Please Login or Register  to view this content.

While the TEXT does get interpreted as an actual duration for values longer than 24h, converting it back using TIMEVALUE still results in a detection of "Not TE." Worse, once a decimal exceeds the value of approximately 416.6666... the TIMEVALUE returns "#VALUE!", since the value exceeds 10,000 hours. Using hh:mm:ss avoids this issue.

Try

in B18 and copy across

=IFERROR(IF(TEXT(B2,"##.#####")+0=B2,B2,B2*24),B2)


Decimal times

This is an excellent suggestion and provides the value I want nearly without fail. The only drawback I see after some quick testing is that the detection fails for decimals with more than four significant digits, due to a rounding difference. I will unpack it to show how. First there is TEXT(B2,"##.####"). This evaluates the input value (represented by B2) to a formatted text entry. Adding zero to that converts it back to a number. The next step is to test whether this is equal to the input. If so, then the number was a decimal and we can write the value directly. Otherwise, we can assume time format and we convert it to a decimal value in hours by multiplying by 24.

The IFERROR is there to circumvent issues with numbers that don't work. For instance, zero yields an error because TEXT(0,"##.####") evaluates to ".", to which adding zero gives an error. This case is trivial to solve; we can just write the initial value directly.

The failure comes as mentioned with rounding errors. I already had the example of 416.66666 as input (because of finding the threshold for the time value of 10,000). This evaluates via TEXT to 416.6667, which when converted to a number and compared to the input is not equal, creating a false positive for conversion to decimal.

The following formula would fix this for decimals, since we are rounding to four decimal places in TEXT:

Formula:

Please Login or Register  to view this content.

Unfortunately, it always fails for time-formatted input. Since ROUND converts time format to a decimal value, the IF compares the original time value to its rounded decimal value.

I will think about how else to modify this so the solution is independent of the number of decimals in the input. Any further suggestions are welcome!

An input of 4.66666 coverts to 4.66666 BUT with a number format of 4 decimal places will DISPLAY as 4.6667

use

=IFERROR(IF(TEXT(K2,"##.#######")+0=K2,K2,K2*24),K2)

if you want more decimal places: with recurring numbers like 4.6666666 you will need to decide where to "round"

See K and L in attached.

This is the immediately obvious solution and works, I just don't like the hidden trap of creating a false datapoint when surpassing an arbitrarily chosen number of decimal points. Unless I find something better, I think it will do as a practical solution. Since I am writing documentation, I can of include this to call attention to the limitation.

So allow me to thank you for extremely fast and competent help!

Thanks,
Mike

P.S. – When using TEXT("##.####",K2), if K2=416.66666, I get 416.6667. This is not just what's displayed, it's the actual value of the cell, see K14 in the attachment. Add a zero and you get the same, as in K15. It would seem TEXT rounds before converting to text.

As i explained .

=TEXT(K2,"##.#######")+0

you will get 416.66666

Okay, I didn't read the explanation as "in the following" but as "that was like this, but you can do it this way."

Thanks again