1. ## Floating date (XP)

I have a graph depicting date against storage on a server.

I would like to place a line ( blue one in my diagram) that shows the date now. I have tried adding a series pointing to a value now() and an arbitary figure to plot but it comes out nonsense, any ideas how I can get a "today" line?

2. ## Re: Floating date (XP)

The easiset way is with an XY scatter.

Then you just add a series that contains 2 XY coords:
The 2 X have the formula (=Today())
The first Y has a value of zero(0), the second Y can have a set value for the Max, or calculate it from the MAX of the other range.

Using an XY also "fixes" the problems you will see in your data and the "trending" since your dates are not evenly spaced. Your "dates" seem to be categories and spaced as you have data, not spaced based on the actual date of the point (which it should be)

Steve

3. ## Re: Floating date (XP)

Thanks SteveI have made the changes you suggest but it has gone a bit awry!

The source data is formatted to to dd/mm/yy and now I am getting rubbish dates!!!

starting at 0/0/01 and going to 15/3/23, what do I do to get the date formats back?

4. ## Re: Floating date (XP)

Cancel my last, I had a load of na() in the source data at specific dates where readings were not taken and this seems to have upset the dates on the x axis

5. ## Re: Floating date (XP)

#NAs should not "upset the dates" at all (they will be "ignored". I use #NAs a lot in XY scatter for this reason.

Steve

6. ## Re: Floating date (XP)

Well it appears the format of the x-axis is set to Minimum = 0 and maximum =120. The graph sees the date as a numerical value, I am really confused

7. ## Re: Floating date (XP)

Yes, dates are numerical. That is what spaces them correctly and does what you asked about doing.

Dates may be large numbers (unless you are looking at dates from 1900)

If you have a zero in them, then perhaps they are text and not actually numbers. I would have to see the data to troubleshoot the problem more.

Perhaps, I do not understand what you are trying to accomplish...

Steve

8. ## Re: Floating date (XP)

Hi Steve

Just to put closure on this...I found the problem. The original wb I received had a series of dates in it the date column said something like this: Thurs 03/11/05 on checking the format of the cell it stated it was a custom date....on investigating it was actually text as there were a load of spaces in front of it. I tried re-formatting but it wouldn't have it.

My ultimate goal was to have a graph similar to what I have shown in the diagram but have a "floating date" that could be changed with a spinner. The way I did this was have the dates in Column A and values in Column B. I then created a spinner and linked it to cell J9.

In cell E5 I wrote this formula:=OFFSET(A1,J5,0,1,1), cell F5 was 0
Cell E6 =E5 and cell F6 =2500

I charted the values in columns A and B and then created a second series for E5, E6 and F5, F6.

When I clicked the spinner my floating date moved across my chart. I have attached it for others to view.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•