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.

date and time interval in excel

Discussion in 'Business Applications' started by The G-man, Sep 14, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. The G-man

    The G-man Thread Starter

    Joined:
    Jun 3, 2001
    Messages:
    140
    Hi, i'm trying to calculate the amount of time between a scheduled date and time and an actual arrival date and time. I have a query that pulls the data and exports it to excel in the following format.

    ACT DATE ACT TIME SCH DATE SCH TIME
    9/8/2004 00:02:33 9/7/2004 23:47:00
    9/8/2004 00:06:00 9/3/2004 23:35:00


    I need to calculate the amount of time an arrival is late. I'm not sure the best way to do this. Can anyone help???

    Thanks :confused:
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Are each of the dates and times stored as seperate cell entries or is the date and time combined together in one cell?


    Rollin
     
  3. The G-man

    The G-man Thread Starter

    Joined:
    Jun 3, 2001
    Messages:
    140
    They are all stored as separate entries. That's why it is a problem, i was trying to find a way to concantenate to calculate the difference, i'm just not sure what is the best approach.

    Thanks for trying to help!
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The easiest thing to do is to use two additonal helper columns. One column would hold the number of full days between the two dates and the other column would hold the difference of hours, minutes, and seconds. Here is a sample workbook I put together to show you how to do this.


    Rollin
     

    Attached Files:

  5. The G-man

    The G-man Thread Starter

    Joined:
    Jun 3, 2001
    Messages:
    140
    Thanks Rollin! That is excellent. This will work for what i'm trying to do! I am curious though, is there a way to exclude weekend days so that they are not included in the time late. In other words, if i was supposed to have something arrive on friday, and it comes in on monday, is there a way to have it calculate it only being 1 day late? That would be nice but not necessary. I do appreciate all your effort!

    Thank you very much!
    (y)
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    If Rollin has:

    =a1-b1
    to give you time between

    you need
    =NETWORKDAYS(A1,B1)

    To use the NETWORKDAYS function, you must have the Analysis Toolpak turned on under Tools-Addins.
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    So if weekend days (SAT & SUN) are not counted, then when does late time begin being adding again? It sounds very confusing to exclude Weekends and it will not be simple to accomplish what you want without resorting to some Visual Basic code.

    Lets say scheduled time is 22:00:00 on FRIDAY September 3th and the actual arrival is 6:00:00 on MONDAY the 6th.

    How would you caluculate this? Would you count 22:00:00 - MidNight (2 hours) and then start counting time again from Sunday Midnight to Monday 6:00:00 (6 hours)

    Be a little more detailed on exactly how you would manually calculate the late time over the weekends. Include some sample weekend data and their respective calculations as you would manually figure them.


    Rollin
     
  8. The G-man

    The G-man Thread Starter

    Joined:
    Jun 3, 2001
    Messages:
    140
    I was working on the spreadsheet that Rollin sent and i have one problem, it checks to see if the arrival date is less than the sched. date, if it is then it adds a full day to the arrival. This only works if midnight is inbetween the two times. So if i have an arrival at 2 am and it is scheduled for 8 am, it is telling me that it was 18 hours late as opposed to 4 hrs early. I'm not sure how to fix this.

    I will try the Networkdays, but that really isn't a big deal. I just need to get the correct time intervals for late arrivals.

    Thanks for helping, it is much closer than what i had before your help!
     
  9. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    I'm not sure I want to bother trying to figure this out without an actual data file from you.

    It only has to have 2 or 3 records. But if we figure this out as though you've got dates and times, and you've actually got text, we're wasting our time. Can you get us an actual sample?
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    OK, I'm working on a new solution for you now. I have already added lots more logic to the formulas and added conditional formatting to change the cell colors to green if there are early arrivals. I am testing data now and will try to post a solution for you later.

    Rollin
     
  11. The G-man

    The G-man Thread Starter

    Joined:
    Jun 3, 2001
    Messages:
    140
    I do really appreciate it! I know you have already spent alot of time on this already.

    Thanks!
     
  12. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Here is the updated workbook. I didn't get a chance to thoroughly test it but I did include six sample scenarios you may encounter. The formulas have changed in the cells and conditional formatting has been applied to the two columns containing the days and time amounts. The data in these two columns will be highlighted in green if a shipment is early. The conditional format has only been applied to the cells containing data so you must copy it and apply it to any new rows of data that you add.

    Sorry, I did not get a chance to try to work on your problem regarding weekend data, but this should give you something to start with. If I get a chance next week, I'll try to post a solution that excludes weekend data. Keep us posted...For now I'd like to know if the new workbook I posted is calculating everything properly.

    Rollin
     

    Attached Files:

  13. The G-man

    The G-man Thread Starter

    Joined:
    Jun 3, 2001
    Messages:
    140
    This is a huge help. The weekend thing is just an added bonus. I will check out what you have, it seems to cover all the scenarios. I really do appreciate all your work!

    Thanks again!
     
  14. 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/274051

  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