1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: excel graphing using time scale axis

Discussion in 'Business Applications' started by ronyace, Apr 4, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. ronyace

    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:
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    ronyace:

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

    XL Guru

    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
     
  4. ronyace

    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
     
  5. XL Guru

    XL Guru

    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
     
  6. XL Guru

    XL Guru

    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
     
  7. ronyace

    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 :)
     
  8. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/217039

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice