0

I have a series of values and the date and time they were recorded:

   Date    | Time  | Value
--------------------------
    ...    |  ...  | ...
27/10/2021 | 11:42 |  18
27/10/2021 | 21:08 |  24
28/10/2021 | 16:31 |  20
29/10/2021 | 00:19 |  19
    ...    |  ...  |  ...

I would like to plot it as a time series, but I can't find good ways to handle the datetimes.

I believe I need to combine dates and times into a single cell. But how do I do that? I could find the functions DATE, DATEVALUE, TIME and TIMEVALUE to handle dates and times independently, but nothing to handle them together.
The support for datetimes must be there though, because the function NOW returns a datetime, because some charts imply they support "date and time" and because some functions are able to work with datetimes.

I tried to concatenate a date and time together them as strings with the formula:

CONCATENATE( TEXT(A2, "dd/mm/yyyy"), " ", TEXT(B2, "hh:mm:ss") )

This spits out a string in the same format of NOW(), but these strings aren't fully treated as datetimes: I'm unable to change their format and the "Timeline chart" refuses to handle them: it says "First column must contain date, or date and time.".
...Yet some functions can work with these strings as if they were valid datetimes: I can subtract them and obtain the correct time difference, or use functions such as HOUR or MONTH to extract the correct value.

1 Answer 1

1

A simple:

=A2 + B2

retrives in column C the timestamp that is recognized by your "Timeline chart"

enter image description here

1
  • I can't believe how simple this was. It works perfectly. Thank you!
    – peoro
    Oct 31, 2021 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.