Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-01-2022, 05:28 AM
Jules90 Jules90 is offline How to enter dates between ? and ? in the criteria section of a database? Windows 8 How to enter dates between ? and ? in the criteria section of a database? Office 2013
Novice
How to enter dates between ? and ? in the criteria section of a database?
 
Join Date: Apr 2019
Posts: 24
Jules90 is on a distinguished road
Question How to enter dates between ? and ? in the criteria section of a database?

Hello,


Iím learning about Database functions Ė specifically DSUM - and Iím stuck at how to enter in the Ďcriteria sectioní dates between e.g. Sum of sales from 18th October 2014 to 17th October 2015.

Iíve set up the criteria section at the top of the worksheet, above the data table, with several blank lines to enter the criteria into.

One of the fields in the data table is ĎOrder Dateí with a list of dates.

Iím not sure how to enter the criteria Iím looking for, as its between two dates, and Iím not sure how to format this correctly.

Any help would be greatly appreciated. Thanks in advance.
Cheers
Reply With Quote
  #2  
Old 07-01-2022, 07:26 AM
ArviLaanemets ArviLaanemets is offline How to enter dates between ? and ? in the criteria section of a database? Windows 8 How to enter dates between ? and ? in the criteria section of a database? Office 2016
Expert
 
Join Date: May 2017
Posts: 713
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
Default

Code:
=SUMIFS(SumRange,OrderDateRange,">=" & DATE(2014,10,18), OrderDateRange, "<=" & Date(2015,10,17))
NB! SumRange and OrderDateRange must be of same dimension, i.e. must have same number of rows!
Reply With Quote
  #3  
Old 07-01-2022, 08:27 AM
Jules90 Jules90 is offline How to enter dates between ? and ? in the criteria section of a database? Windows 8 How to enter dates between ? and ? in the criteria section of a database? Office 2013
Novice
How to enter dates between ? and ? in the criteria section of a database?
 
Join Date: Apr 2019
Posts: 24
Jules90 is on a distinguished road
Default

Hi there,

Thanks for replying to my post Ė its much appreciated.

Iím sorry if I was unclear in my description of the problem Iím having. Itís a DSUM function Iím learning at the moment. It has 3 arguments: database,field,criteria.

Iíve got a table of data with headers. Iíve copied and pasted the headers into another section of the worksheet with blank rows below to enter my criteria.

There are 7 fields in my table i.e. Order Date; Region; Rep; Item; Units; Unit Cost; Revenue.

Iíve created a text box labelled DSUM and the cell next to it is where I have my DSUM formula entered. Iíve managed to work out how to calculate other questions e.g. calculate total revenue for the region ĎCentralí but only for the item ĎPení.
I enter ĎCentralí below the Region heading in the Criteria Section and ĎPení below the Item heading and arrive at the correct result.

What Iím having difficulty with is how and where to enter from 18th October 2014 to 17th October 2015. I presume it needs to read something along the lines of: >=18/10/2014 and <=17/10/2015? As Iím entering two dates in one box under Order Date in the Criteria Section, Iím not sure how I should type this?

I hope Iíve explained this a little better? Thanks
Reply With Quote
  #4  
Old 07-01-2022, 09:12 AM
ArviLaanemets ArviLaanemets is offline How to enter dates between ? and ? in the criteria section of a database? Windows 8 How to enter dates between ? and ? in the criteria section of a database? Office 2016
Expert
 
Join Date: May 2017
Posts: 713
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
Default

Have never used DSUM(), and after I had a look at it, I'll never use it. It does nothing that SUMIFS() doesn't do, and you are less free at design with it.

And before SUMIFS() was introduced, I used SUMPRODUCT() instead.
Reply With Quote
  #5  
Old 07-01-2022, 09:33 AM
Jules90 Jules90 is offline How to enter dates between ? and ? in the criteria section of a database? Windows 8 How to enter dates between ? and ? in the criteria section of a database? Office 2013
Novice
How to enter dates between ? and ? in the criteria section of a database?
 
Join Date: Apr 2019
Posts: 24
Jules90 is on a distinguished road
Default

Thanks anyway...Cheers
Reply With Quote
  #6  
Old 07-05-2022, 12:26 AM
Debaser's Avatar
Debaser Debaser is offline How to enter dates between ? and ? in the criteria section of a database? Windows 7 64bit How to enter dates between ? and ? in the criteria section of a database? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 196
Debaser will become famous soon enough
Default

Quote:
Originally Posted by Jules90 View Post
As Iím entering two dates in one box under Order Date in the Criteria Section, Iím not sure how I should type this?
You need to duplicate the Order Date header in another column and put the >= criterion in one column and the <=criterion in the other.

Alternatively, you can use a formula criterion instead.
Reply With Quote
  #7  
Old 07-05-2022, 05:11 AM
Jules90 Jules90 is offline How to enter dates between ? and ? in the criteria section of a database? Windows 8 How to enter dates between ? and ? in the criteria section of a database? Office 2013
Novice
How to enter dates between ? and ? in the criteria section of a database?
 
Join Date: Apr 2019
Posts: 24
Jules90 is on a distinguished road
Default

Hello,
Thanks so much for replying to my post. Itís been causing me no end of a headache!

To explain, Iím doing a free online Excel course. The module Iíve been working on is how to use database functions. It doesnít cover everything and just skims through certain topics. It only showed how to use the DCOUNT function, and while trying out different database functions e.g. the DSUM for example, which I found more interesting and useful, I came across a website www. wallstreetmojo.com and downloaded a DSUM examples template. It had a number of questions with the results shown in Revenue boxes on the right hand side of the worksheet. The formulas were the same with database, field, criteria arguments entered.

Question 5 Ė ďSum of sales from 18th October 2014 to 17th October 2015Ē required two separate Order Date boxes to enter >=18/10/2014 and <=17/10/2015 as youíve explained here. The problem was that in the Revenue box with the formula showing as correct, in my downloaded template worksheet the result was displaying as 0. I checked online to the original website and it had a result of 8646.

I tried various ways of creating the Criteria Section e.g. above the table; to the right as shown in the website; but nothing produced the correct result. When I click in the Revenue cell in the downloaded template worksheet, the correct formula is showing. Iíve checked and double checked this!

In desperation, I tried another way to see if I could get the correct result matching the website. I filtered the dates and then added a total row to get the sum, and in the Revenue column Ė voilŗ!

Iíve tried what youíve described here and duplicated the OrderDate header to another column and entered the dates i.e. >=18/10/2014 and <=17/10/2015. The duplicated column is named OrderDate 2 by default. I tried it duplicating just the column header in the criteria section, and not adjusting the table headers. I also tried it by duplicating both, to keep the headers identical in both table and criteria section. Still not getting the 8646 as the result! Could this be anything to do with me having Home & Student 2013 and not the full Office 365 version when it comes to entering two criteria in the same column?

Anyway, when it comes to entering the arguments in the Function Arguments dialogue box, how would I enter in the field box, the two OrderDate column numbers or headings?

Iím also not sure what you mean by using a formula criterion alternatively? Instead of using the Function Arguments box, I tried to enter the formula in the formula bar, but not getting this rightÖtoo many arguments!

Sorry this is so long, but I really want to be able to use this database function as itís supposed to be used. Iím stubborn that way!! Many thanks.
Cheers
Reply With Quote
  #8  
Old 07-05-2022, 03:14 PM
Debaser's Avatar
Debaser Debaser is offline How to enter dates between ? and ? in the criteria section of a database? Windows 7 64bit How to enter dates between ? and ? in the criteria section of a database? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 196
Debaser will become famous soon enough
Default

It sounds like you have your criteria range set up as a table. You don't want that because it will stop you from duplicating column headers.

It would be simplest if you post your workbook so that we can see where you are going wrong currently. I will then also show the formula criterion version. It's a little odd, but can make complex criteria easier to set up.
Reply With Quote
  #9  
Old 07-06-2022, 03:11 AM
Jules90 Jules90 is offline How to enter dates between ? and ? in the criteria section of a database? Windows 8 How to enter dates between ? and ? in the criteria section of a database? Office 2013
Novice
How to enter dates between ? and ? in the criteria section of a database?
 
Join Date: Apr 2019
Posts: 24
Jules90 is on a distinguished road
Default

Success! I converted the table back to a normal range and then duplicated the Order Date column as you suggested, and then copied and pasted the criteria headers elsewhere on the worksheet. While doing this and looking at the example template Iíd downloaded, I noticed the table dates were in the UK date format but the criteria dates entered were in US date format. When I changed them to UK format the result immediately changed from 0 to 8646.

For some reason when clicking on Ďenable editingí after scanning the downloaded template, this is when the value changed. Before that it shows as 8646! So, thatís good to know for the future to double check everything as Iím not 100% observant when it comes to that sort of thing.

Youíve been a great help. I wouldnít have worked this out myself or discovered the date format discrepancy if I hadnít gone back to look at this again and duplicate the column etc. Thanks a million.
Cheers
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to enter dates between ? and ? in the criteria section of a database? How to enter 'Page 1' from the footer menu in the right hand section, and not the middle section? Jules90 Excel 2 08-11-2021 06:44 AM
How to filter between two dates in MS Query criteria when the dates are text ("20180903", etc.) kingmb Excel 2 09-11-2018 01:08 AM
How to enter dates between ? and ? in the criteria section of a database? Create a unique list of values that match a criteria, sorted in order of another criteria BradRichardson Excel 2 01-03-2017 12:25 AM
Why do you need to enter your password twice when connecting to an SQL Server database? Dedication99 Mail Merge 0 08-05-2016 02:18 AM
How to enter dates between ? and ? in the criteria section of a database? Is there a way to detect duplicate dates as I enter them? Karin Excel 1 03-04-2011 12:56 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft