## 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?

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

DuaneHook'D on Access

MS Access MVP

## RE: Differences in Time Calculations from Excel to Access

## RE: Differences in Time Calculations from Excel to Access

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

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

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

DuaneHook'D on Access

MS Access MVP

## RE: Differences in Time Calculations from Excel to Access

=(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

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

DuaneHook'D on Access

MS Access MVP

## RE: Differences in Time Calculations from Excel to 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

DuaneHook'D on Access

MS Access MVP

## RE: Differences in Time Calculations from Excel to Access

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

## RE: Differences in Time Calculations from Excel to Access

DuaneHook'D on Access

MS Access MVP

## RE: Differences in Time Calculations from Excel to Access

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

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?

DuaneHook'D on Access

MS Access MVP

## RE: Differences in Time Calculations from Excel to Access

## CODE

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

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

DuaneHook'D on Access

MS Access MVP

## RE: Differences in Time Calculations from Excel to Access

Thanks!