Summing date ranges (1 Viewer)

Emma35

Registered User.
Local time
Today, 11:20
Joined
Sep 18, 2012
Messages
455
Hi there,
I haven't started the database yet but i'm wondering if it's possible to add date ranges together to get a total number of days. For example : If a person worked on a particular task from 1/1/19 to 6/1/19 and again on 2/3/19 to 7/3/19 and then again from 4/6/19 to 13/6/19....would it be possible to design a query to work out the total number of days the person spent on that task ?
I wasn't sure if this could be done with a query so i put it in this section.

Ps...the table would contain a StartDate and EndDate field to reflect the start and finish dates on each occasion

Thanks (Dates in UK format)
 

isladogs

MVP / VIP
Local time
Today, 18:20
Joined
Jan 14, 2017
Messages
18,186
Yes. The number of days in each date range would be individually calculated then summed.
You might find it easier to create a function to do this. If so, you will need to convert dates to US format in the function code
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:20
Joined
Oct 29, 2018
Messages
21,358
Hi. It should be possible but may have to generate a row for each day worked to count it, which is also possible using just a query.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 14:20
Joined
Jan 23, 2006
Messages
15,364
Emma,

You may find some insight in the Similar Threads identified at the bottom of this page.
 

Cronk

Registered User.
Local time
Tomorrow, 05:20
Joined
Jul 4, 2013
Messages
2,770
An alternate to Datediff is to subtract the two dates. But but be aware for someone worked July 6, 7 and 8 (3 days) that


DateDiff("d", #July 6 2019#, #July 8 2019#)
as well as

#July 8 2019# - #July 6 2019#
both give 2
 

Emma35

Registered User.
Local time
Today, 11:20
Joined
Sep 18, 2012
Messages
455
Thanks for the suggestions guys.....still not really sure how to go about this though. I've used the DateDiff function before but just for two dates and not to add time periods together. What i'm looking to do is to have a form where a user enters two dates and the query will return a list of people (highest to lowest) who have spent the most time on that task.
Apologies for taking a while to repost but I've a family member in hospital and it's a little difficult

Thank you
 

plog

Banishment Pending
Local time
Today, 13:20
Joined
May 11, 2011
Messages
11,611
Now you've significantly moved the goalposts, this isn't what you listed initially. Your first post just wanted to calculate total days over multiple rows without criteria. Now you want to apply criteria to individual rows, sum them and then find the person with the highest total.

For this you are going to need 2 queries, subQuery and Query. subQuery will be based on your table and use a DateDiff function to calculate total days wihtin the input timeframe. Query with sum those values and return the results you want.

Because of your added complexity I suggest you create a function in a module to determine the correct amount of worked days within the timeframe. It would take the timeframe dates as well as the Start/End dates from your tables and return the total days worked that fall within the timeframes.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:20
Joined
Jan 23, 2006
Messages
15,364
You'll have to ensure you are adding work hours/days. Seems you would ignore weekends and holidays, but you know the requirement better than readers.
 

Mark_

Longboard on the internet
Local time
Today, 11:20
Joined
Sep 12, 2017
Messages
2,111
One thing to be very careful about; Periods that start BEFORE or end AFTER your date range.

John work from July 21st to September 7th. How do you want to reflect his time if your asking for only the month of August.

You will want to look at all end dates AFTER your start date and all start dates PRIOR to your end date. As such you may have to have an additional query that simply gives you your data (with start/end adjusted within the query based on passed start/end) to drive everything FIRST.
 

Emma35

Registered User.
Local time
Today, 11:20
Joined
Sep 18, 2012
Messages
455
Ok sorry if i've confused things a little. I've just had a meeting with the guy who needs the database and it's a bit clearer now what has to be done. I'll post a basic sample database in the morning (just a table with some fields and info) and outline what i'd like to do exactly and hopefully someone can up with a query to extract the information i need.
Thanks again for your time with this
 

Emma35

Registered User.
Local time
Today, 11:20
Joined
Sep 18, 2012
Messages
455
Ok i've created a table and a query where i can work out how many days each person has spent on a task but how do i sum them together ? I'd like to add each persons days together to come up with a total for that person.
I've been told that the User would like to select a persons name from a combo box on a form and view all their StartDates and EndDates and also their total time spent on a task

Thanks all for your help
 

Attachments

  • Test2.zip
    21 KB · Views: 44

Cronk

Registered User.
Local time
Tomorrow, 05:20
Joined
Jul 4, 2013
Messages
2,770
Drop TaskID and Start/Enddates from the query. Group on EmployeeID (not sum) and sum the DaysWorked, your query will show the aggregate for each employee.


But if you read my post earlier, you will see that for accuracy you need
Code:
DaysWorked: [EndDate]-[StartDate] + 1
 

KlausObd

New member
Local time
Today, 19:20
Joined
Jul 19, 2019
Messages
2
Hi,
Dates: a) The datetime format in MSAccess always stores a point in time - internally stored as type double (the values before the . are the number of days since the 30.12.1899 (Bill Gates GrandFathers birthday) and the numbers after the . are the part of the day. 0 = midnight, date only no time 0.5 = High Noon etc. And the dates are only DISPLAYED as dates.
b) i for myself once did a day-table, with one record for each day between 2000 and 2100. It also contains the German holidays and the info if weekday or not etc. Extremely useful for date calculations as you easily can create a cross join between a start and an end date and that day table, containing all the dates in between, or for weekday calculation etc. Even if the table contains several records, it normally just sits in the corner - normally no updates are done on that ... I always use it, if i calculate days ...
mfg Klaus
 

Emma35

Registered User.
Local time
Today, 11:20
Joined
Sep 18, 2012
Messages
455
Great it's working now...thanks to everyone for your your advice.

Cronk i have one more quick question.....How does taking out the date fields make the query work ?.....and will i have to create a second query if i want to see all the StartDates and EndDates for each person ?

Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:20
Joined
Jan 23, 2006
Messages
15,364
Glad you have it working.
 

Cronk

Registered User.
Local time
Tomorrow, 05:20
Joined
Jul 4, 2013
Messages
2,770
Cronk i have one more quick question.....How does taking out the date fields make the query work ?.....and will i have to create a second query if i want to see all the StartDates and EndDates for each person ?
If you group on the date fields, the query will show every single instance of each date range and employee.


If you create another query including the date ranges, you could join the two queries on EmployeeID and show for each employee, every date range, the days worked for that range, as well as the total for all ranges for that employee.
 

Users who are viewing this thread

Top Bottom