0

My idea is to have this 'date format' cell in order to graph prices that corresponds to each hour and have a around 10.000 rows of information for every year. This is what it looks like when I download these prices for any year

prices for any year

As you can see, there will be between 720-744 different prices through a month and I'd like to see the variation in time (last 6 years for example) of these prices, for example

Example

I want to do this because of what I've read for these charts, but if you have a better/efficient approach I would be truly appreciated to hear it.

  • 1
    hour/day/month without a year component have no sense. When you add a year component the task became trivial. – Akina Aug 7 '18 at 5:07
  • In Column C write Hours like 20:00 and apply 24 Hrs format like [h]:mm then in D2 you may write this , =TEXT(C2,"HH, ")&TEXT(B2,"DDd, ")&TEXT(A2,"MMM") , you get 20, Wed, Jan` – Rajesh S Aug 7 '18 at 7:01
  • Your volume of data doesn't exceed Excel's limits, but that will get slow. I think the chart data limit is 32,000 data points (at least as of a few years ago). But that much data in an Excel chart will be a mess. You might want to explore other tools designed to handle this quantity of data. Also, consider ways to reduce data points for charting (aggregate, sample, etc.) – fixer1234 Aug 7 '18 at 7:41
2

The functions DATEVALUE and TIMEVALUE converts a date or time that is stored as text to a serial number that Excel recognizes as a date or time. You could simply add the two to get the needed DateTime value.

=DATEVALUE(B3&"/"&A3&"/2018")+TIMEVALUE(C3&":00:00")

You might need to generate the correct string to match you locale (ie. using YMD format):

=DATEVALUE("2018."&A3&"."&B3)+TIMEVALUE(C3&":00:00")

Set the custom format hh/dd/mm on the cells containig the results.

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.