Differences in Time Calculations from Excel to Access 1

[JustJosh478]

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?

 

[dhookom]

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

 

[JustJosh478]

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?

 

[Andrzejek]

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.

 

[JustJosh478]

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.

 

[dhookom]

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

 

[JustJosh478]

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.

 

[dhookom]

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):

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

Duane
Hook'D on Access
MS Access MVP

 

[JustJosh478]

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?

 

[dhookom]

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

Duane
Hook'D on Access
MS Access MVP

 

[JustJosh478]

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?

 

[JustJosh478]

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

 

[dhookom]

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

Duane
Hook'D on Access
MS Access MVP

 

[JustJosh478]

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?

 

[dhookom]

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

 

[JustJosh478]

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.

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

 

[JustJosh478]

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.

 

[dhookom]

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

 

[JustJosh478]

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!