date and time interval in excel

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.

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:
 
Joined
Sep 4, 2003
Messages
4,916
Are each of the dates and times stored as seperate cell entries or is the date and time combined together in one cell?


Rollin
 

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!
 
Joined
Sep 4, 2003
Messages
4,916
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
 

Attachments

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)
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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!
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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?
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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!
 
Joined
Sep 4, 2003
Messages
4,916
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
 

Attachments

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!
 
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