# Solved: excel graphing using time scale axis

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

#### ronyace

I am trying to graph data that contains the date on the x-axis and the time on the y-axis. The bar graph will show times items were received that day. However, some items are received before midnight and even though they are counted as received on the next days shipment. When graphing this, the bar will go all the way to the top of the graph and does not show that the items were received before midnight (0:00 AM). How can I set up the graph to show the x-axis starting at 0 time but have times going down to noon on the previous day and ending at the top of the graph around noon on the current day?

#### Anne Troy

Anne
ronyace:

Could you possibly reword your question? Your one sentence appears incomplete, and I cannot make out quite what you want...

#### XL Guru

I think you mean that items received before noon belong to that day, items received after noon belong to the next day ; or something like that.

Assuming that column A = Date Received & column B = Time Received. In Excel, each day counts as 1. Thus today's date = 38082 (# of days since 1-1-1900), and 06:00 = 0.25 (1/4 of a day). So, today + 06:00 = 38082.25.

So you could add a formula column to calc which day an item received is proper to, according to the date and time, using INT function. For example, in C2

=IF((B2+A2)-INT(B2+A2)<0.5,A2,A2+1)

With this, if time in B2 is less than 12:00, it'll show A2 (Date Received). If B2 = 12:00 or later it'll show A2 + 1 (day).

HTH,
Andy

#### ronyace

Thanks, I'll try that.

Let me rephrase my question so it makes more sense. The data page looks like this:

Date Time in
1/1/04 02:45
1/2/04 02:05
1/3/04 23:55
etc.......

The problem occurs when graphing data for 1/3/04. It is counted as received by us on the third even though it came in early at 11:55 (23:55) on 1/2/04. I want to graph the date column on the x-axis and the and the time in on the y-axis. The graph thinks that time 23:55 was on the 3rd even though it was 5 minutes earlier than midnight.

I hope this clears up what I need. I will try your solution, but please let me know if there are any other methods to use.

Thanks,
Ronyace

#### XL Guru

Hi Ronyace.

>> I hope this clears up what I need.

Slightly, but I can't really see a way round. Somehow Excel will need to be told if an arrival time is in fact early ; it's not psychic. So you'd need an extra column to mark "Early" or "On Time". From there, you might need another column for the actual data to be charted using some kind of formula (if extra column = "Early", do something, if not do something else).

So what I have is 3rd Jan in A4, 23:55:00 in B4, "Early" in C4 and

=IF(C4="Early",-(1-B4),B4)

in D4. The formula in D4 returns -00:05:00. And I do mean minus 5 minutes. You can force Excel to handle negative time if you apply 1904 date system, via Tools -- Options -- Calculation (BIG NB ; do NOT turn this on in a workbook already containing dates, as it will adjust them by about 4 years).

So you could perhaps generate a value of minus 5 minutes on 3rd Jan, i.e. 5 mins before midnight. Exactly how this might chart I can't say ; try it & see.

HTH,
Andy

#### XL Guru

That said, you might be able to get away with a column of "Real_Date" formulas, e.g. in C4

=IF(B4<0.5,A4,A4+1)

Translation - if Time_In in B4 is less than 0.5 (midday), Date_In = date in A4 ; if not, Date_In = date in A4 + 1 (next day).

Rgds,
Andy

#### ronyace

Thanks for all the help, I will let you know if this works. This forum is great and I appreciate all the quick responses.

Ronyace

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

As Seen On