Google Sheets, line graph, moving date line

[TinyDamooge]

Hi there,

I'm trying to add a moving date line across my google sheet chart.
Simply a vertical line the cuts across the chart showing a clear indication of todays date.

I've done it in excel, by one of the two following methods:

-By creating a new series, which is actually a line / scatter plot on the secondary axis with the values of 0,1 only.
-By creating a new series, which is actually a single data point with a massive vertical error bar to give the appearance of a line.

But I can't work out how to do it on a Google Sheets chart,
Can anyone be of assistance?

Best regards

TinyDamooge

Edit: uploaded a .xlsx of how I might achieve this in excel.

[janmorris]

Heres how to do it:

Put the TODAY date at the top of each stack of dates, and leave blanks beside it in the alpha and beta columns.
(note: i added 2 days to it so that it sits in the middle of the chart so you can see it works properly)

Drop in a Line Chart or Smooth Line Chart

Add in your Data ranges including the row that has the TODAY date, so that the first cell of the other columns includes a blank cell
AND and in a range just for the TODAY date cell

Your ranges will look something like this:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Combine the ranges Horizontally

Add the full date range for the X-Axis

in your examples it would be either:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Add in the Series for the column C and column D ranges, AND add in the single cell for the TODAY date

Enable Use column B as labels

Switch to Customize

Go to the Series section

Change Apply to all series to Line 3

Change the line colour to red
Ensure opacity is 100%
Ensure point size is None
Enable Error Bars
Change percent to 100

Go to the Vertical Axis section

Set Max to 600
Set Min to 0

Screen Shot 2022-04-07 at 11.18.01 pm.png

[TinyDamooge]

Hi janmorris

It took A LOT of playing around, (The actual data is much more complex that that I gave as an example)

But I got it to work - thankyou! (I'll mark as solved)


Note: I only managed to get my own sheet working after I transposed the large amounts of horizontal data into vertical data. Weird!

Ta

Luke

[janmorris]

im glad you got it to work out. thanks for the reps, much appreciated.

from my experience, the trick seems to be to include the value you want (as the error bar) with the other data. it doesnt need the x and y values because as you add the series this single value will be turned into the error bar (vertical line) only.