date and time interval in excel

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

Not open for further replies.

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

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

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

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:

• CalculateTime.zip
File size:
2.3 KB
Views:
34

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!

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

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

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

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

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

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

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:

• CalculateTime2.zip
File size:
2.9 KB
Views:
27

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!

As Seen On