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
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
MONTH to extract the correct value.