INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Differences in Time Calculations from Excel to Access

Differences in Time Calculations from Excel to Access

(OP)
Hello all,

I'm struggling trying to find out why Access is calculating time durations on certain data differently than Access. Here's my story...

There is data in a spreadsheet that looks at time duration based on the NETWORKDAYS code and gives a duration of how long a ticket has been open during business hours. If I grand total that information by location, I get a value.

If I take that same spreadsheet and import it into Access and use Query to grand total the same time duration on the same column, I get different calculations based on the location. For instance, I have 2 locations where my total from Excel matches Access but 2 locations where Excel calculates higher than Access.

What would cause Excel or Access to calculate some of those totals differently, yet be exactly the same on others?

RE: Differences in Time Calculations from Excel to Access

I expect the issue revolves around using a date/time field to report duration. If you display the resulting duration in Access as a time, it will display as a value less than 24 hours.

I always convert the durations to a number of minutes or hours or whatever and then sum. You can format the output/display but it takes a custom function like a more complete DateDiff().

Duane
Hook'D on Access
MS Access MVP

RE: Differences in Time Calculations from Excel to Access

(OP)
Which way is "correct"? I'll need to justify my calculations and why they aren't the same as Excel. How could I mimic the Excel calculation for Access? Is it possible?

RE: Differences in Time Calculations from Excel to Access

Both are (probably) correct.
How much of a difference do you see between the two?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Differences in Time Calculations from Excel to Access

(OP)
Excel Calculates:
Location 1 1198:14
Location 2 66:04
Location 3 67:19
Location 4 370:20

Access Calculates:
Location 1 1030:14
Location 2 66:04
Location 3 67:19
Location 4 322.20

It's puzzling how it calculates perfectly for Locations 2 and 3 but 1 and 4 are off.

RE: Differences in Time Calculations from Excel to Access

The date and time calculations have always been reliable in both Excel and Access. We have no idea how you are calculating time durations. In Access there is no built in NETWORKDAYS.

Can you share your calculations? It is apparent the records with longer duration are not aligned but the shorter ones are.

Duane
Hook'D on Access
MS Access MVP

RE: Differences in Time Calculations from Excel to Access

(OP)
They are using the NETWORKDAYS function in Excel calculate work days AND work times by using this formula:

=(NETWORKDAYS(I2,J2,Holidays))*(Endtime-Starttime)+IF(NETWORKDAYS(I2,I2,Holidays)=0,0,MAX(0,Endtime-MAX(Starttime,MOD(I2,1)))-Endtime+Starttime)+IF(NETWORKDAYS(J2,J2,Holidays)=0,0,MAX(0,MIN(MOD(J2,1),Endtime)-Starttime)-Endtime+Starttime)

Here's a sample row:



Total Time Spent is calculated here by adding both differences and I think that's where it may be falling apart.

RE: Differences in Time Calculations from Excel to Access

You image shows Excel but you haven't provided the Access calculation.

Again durations should be expressed in some unit of measure. Date/time data types store float numeric values with the integer part being the number of days and the decimal part being the time of the day. 0.5 expressed in time format is 1/2 day or 12:00 PM. Formatting 36 hours (1.5) as a time will not display the integer part (whole days):

CODE --> debug

? Format(1.5,"hh:nn") = 12:00 

Duane
Hook'D on Access
MS Access MVP

RE: Differences in Time Calculations from Excel to Access

(OP)
I'm using 2 expressions to get that calculation in Access:

TotalSeconds: (Sum(DatePart("h",[Total Time Spent])*3600+DatePart("n",[Total Time Spent])*60))

TotalTime: Round([TotalSeconds]/3600,0) & ':' & Right("00" & Round((([TotalSeconds]/3600)-Round([TotalSeconds]/3600,0))*60,0),2)

Based on what you said on the previous post, my issue now is that I'm calculating my information on the date/time field created in Excel, which is apparently not recommended. So would the easiest solution be to make the NETWORKDAYS calculated result a numeric field instead of a date/time field? Would it be that easy or would there be other steps involved?

RE: Differences in Time Calculations from Excel to Access

DatePart("H",...) will always show less than 24 hours in Access.

Duane
Hook'D on Access
MS Access MVP

RE: Differences in Time Calculations from Excel to Access

(OP)
So while I was waiting for Dhookum's response, I changed the field times to General in Excel, imported that sheet and pointed my Access calculations to that sheet and I got the same numbers that I posted in Access before.

To me, that confirms what dhookum said all along. Using the Date/Time field to add durations is a bad practice. Am I right with this thought?

RE: Differences in Time Calculations from Excel to Access

(OP)
Ok... how would I convert 49.9263888888357 into a duration? For some reason, I'm drawing a blank.

RE: Differences in Time Calculations from Excel to Access

What does 49.9263888888357 represent? Is this days or hours or minutes?

Duane
Hook'D on Access
MS Access MVP

RE: Differences in Time Calculations from Excel to Access

(OP)
That number is generated by taking each ticket duration time and converting that field to General instead of Short Time in Excel. If I take that data and import it into Access (or subtotaling in Excel), That would be the grand total of "time" for each location. Part of the reporting I'm building is looking at location performance and I need a summary of time duration by each location. So the 49.9263888888357 would be each record for a location summed as a total duration.



So the DurationfromExcel is the duration in the short time format. Total Time Spent is that same field converted to General. I summed the total time spent to get the 49.9263888888357. How would I convert these back to legit durations in Access?

RE: Differences in Time Calculations from Excel to Access

So 49.9263888888357 is the time duration. If so it would amount to almost 50 days. Does that seem logical?

You wanted to convert this to a duration and I would say it's already a duration. If it's almost 50 days how would you want it to display? Is 49.9 days good or do you want to convert this to hours by multiplying by 24?

Duane
Hook'D on Access
MS Access MVP

RE: Differences in Time Calculations from Excel to Access

(OP)
Ok, I think I'm FINALLY figuring this out. Looking through the forums here, I determined that this number is a seconds calculation. If I take this number and wrap it around this expression, my numbers appear to be correct.

CODE

Round(([Total Time Spent]*24)*60)] 

So I think can use this formula to duplicate the durations created in Excel. I'll keep everyone posted...

RE: Differences in Time Calculations from Excel to Access

(OP)

Quote (Dhookum)

So 49.9263888888357 is the time duration. If so it would amount to almost 50 days. Does that seem logical?

You wanted to convert this to a duration and I would say it's already a duration. If it's almost 50 days how would you want it to display? Is 49.9 days good or do you want to convert this to hours by multiplying by 24?

Yes, I would want to convert these to hours because we are trying to justify manpower to hire additional people. Hours would be more meaningful than days.

RE: Differences in Time Calculations from Excel to Access

It's all about the math and units. I'm not sure about your "seconds calculation" comment since 24*60 would suggest converting to "minutes" from days.

Duane
Hook'D on Access
MS Access MVP

RE: Differences in Time Calculations from Excel to Access

(OP)
Yep! I'm finally where I need to be. You were spot on Duane. I kept the 24 and removed the 60 and now my durations are nearly perfect. There is a little variation past the 2nd decimal but I'm not too concerned with that.

Thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close