Solved: excel graphing using time scale axis

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

ronyace

Thread Starter
Joined
Apr 4, 2004
Messages
50
Please help.
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? :confused:
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
ronyace:

Could you possibly reword your question? Your one sentence appears incomplete, and I cannot make out quite what you want...
 
Joined
Aug 30, 2003
Messages
2,702
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

Thread Starter
Joined
Apr 4, 2004
Messages
50
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
 
Joined
Aug 30, 2003
Messages
2,702
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
 
Joined
Aug 30, 2003
Messages
2,702
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

Thread Starter
Joined
Apr 4, 2004
Messages
50
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. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top