Time & date formats

QuestionEdit

Hello Miguel,

Request your help, for the attached screenshot. I am trying to calculate the average for this data. But due to someformat, it does not do so. What is the formula to change this format?

Best Rajesh.

AnswerEdit

The formulas to ensure that something is a time/date are TIME and DATE. I am not sure if they will be useful in your case, as you need to input them each part of the time and date that you want them to calculate as parameters.

One workaround can be to manually convert the ones that give errors. You can detect them by using a formula like this on column B: =A1*1 If the formula gives an error, that number is a target for conversion. If you have a large group of them, and they all share the same format, you can try to determine a formula using the TIME/DATE functions for that specific format, and that could be doable.