How to create an Excel graph correlating dates and times

Question

I have created a spreadsheet with columns for

A: Year - integer

B: Month - integer

C: Day - integer

D: Date - of the form =DATE(A3,B3,C3)

E: Time - a Custom number h:mm

I want to graph for each date a corresponding time; dates along the horizontal axis and time on the vertical.

For sunrise times, it should resemble a sine wave.

But I have not yet found out how; an example appears in the image.

Have tried Line and Scatter graphs without success; what is the secret?

Answer 1

Remove the Date series and use it as label for the horizontal axis.

If you need further help, we need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.

Answer 2

Hi Ronks,

Thats what my graph look like:

• scatter graph with D column formated as text
• scatter graph with D column formated as date
  

Best Regards,

Thomas.L - MSFT | Microsoft Community Support Specialist

Answer 3

Here is a sample file with data from my location

https://www.dropbox.com/scl/fi/3n7t97pr68z45h794bl1v/aabc21b4-8bad-4df3-b6c9-de57b9cdbe8a.xlsx?rlkey=55sk4le37sxgpk9av6lntwero&st=pi610wgc&dl=1

No formulas, just data and chart, day length on secondary axis:

Data source:

https://www.sunrise-and-sunset.com

Andreas.

Answer 4

Thanks to everyone who responded. Am looking at replies and making progress.

More when I have news.

Answer 5

Got it! Thanks to everyone.

I had two problems; one was realizing I had only one series (time) and not two.

The other was navigating the popup context panes for each axis and how to format the axes; I think this has changed since I last worked in Excel.