DSUM with dates

I have a column with some dates and some blanks, and I want to include all 
values in my DSUM that have dates in the row - as well as some other 
criteria.

I can't find a way to specify in my criteria, that it should include the 
date rows and exclude the blanks. I've tried things like '<>""' and '=*' , 
but I can't get it to work.

(It'd even be OK if I could sum all the rows with blanks, because then I 
could use the difference from the overall sum)

I'd be very grateful if anyone can point me in the right direction.

Thanks

M 

0
Michelle
12/17/2009 9:18:40 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1482 Views

Similar Articles

[PageSpeed] 48

=COUNTBLANK(C14:C20)

 - will count the blanks for you. 

If my comments have helped please hit Yes. 

Thanks. 

"Michelle" wrote:

> I have a column with some dates and some blanks, and I want to include all 
> values in my DSUM that have dates in the row - as well as some other 
> criteria.
> 
> I can't find a way to specify in my criteria, that it should include the 
> date rows and exclude the blanks. I've tried things like '<>""' and '=*' , 
> but I can't get it to work.
> 
> (It'd even be OK if I could sum all the rows with blanks, because then I 
> could use the difference from the overall sum)
> 
> I'd be very grateful if anyone can point me in the right direction.
> 
> Thanks
> 
> M 
> 
> .
> 
0
Utf
12/17/2009 11:34:01 AM
Reply:

Similar Artilces:

Formulating dates by weeks
Hello, I would like to use a formula that allows me to type in a date is (lets's say A1) and for it to be carried forward in A2, A3, A4.. and so forth up to thirteen cells. Example: I will type July 1st - July 7th in A1, and automatically A2 will be formulated to have July 8th - July 14th in it. (and so on and so forth) If someone could help me with this.. AWESOME! I am stuck! Thanks Oh, and one more, if it is answerable.. If I have a row, that need a running tallie of only certain cells, (let's say, A1, A6, A12..) is there an easier way to do this other then =SUM(A1, A6, A...

Change requested ship date on SOP and have it update on PO
We do mostly drop ship orders so we have a link to a PO on the SOP. I know when you first enter in a SOP the requested ship date will update the required date on the PO. But, if you change the ship date on the SOP after the link to the PO is created the required date on the PO won't update. Is there a way to get the PO to update automatically? Thanks Jennifer If the system doesn't automatically update - you could create an SQL trigger to update the POP tables. Example of what a trigger can do...........I had to create a SQL trigger for a client that wanted to change the re...

ormula for weeks from a date to today?
Can someone please advise me of a formula that would automatically update the number of weeks from a list of dates to the current date? My daughter just helped me find it... =(DATEDIF(B2,NOW(),"d"))/7 Thanks anyway. "Jenn247" wrote: > Can someone please advise me of a formula that would automatically update the > number of weeks from a list of dates to the current date? > Can someone please advise me of a formula that would automatically update the > number of weeks from a list of dates to the current date? If the dates are in column A, put t...

save .snp file as system date and time
Hi, I'm trying to save a .SNP file in a specific directory and with the current time and date. The file is saved in the specified directory, but he's always saved as today.snp here is the code: Dim stDocName As String Dim today today = Now stDocName = "suporte_encomenda" DoCmd.OutputTo acReport, stDocName, "Snapshot Format", "l:\docs\today.snp" could anyone help me, please??? -- Thanks, Sorry if my english isn''t correct, but I''m from Potugal ;) Emanuel Violante Galeano First off, you didn't define a d...

Date/Time Stamping?
I'd like to be able to automagically enter the date/time. Either into separate cells (one for date another for time) or into a single cell. Nothing complicated - just keeping a list of network response time tests. First column(s) will be Date/Time, the other column will be KBPS. I can do VBA, just don't know where to start. -- PeteCresswell Pete, have a look here, http://www.mcgimpsey.com/excel/timestamp.html -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appre...

Medium Date format and calendar in Access 2007
Hi there Due to complaints from users, I will have to use medium date format for dates instead of Short Date in my forms. The Tables are in the short date format. So the data tables are Short Date and the Forms are Medium Date. When I changed the format of dates from Short date to medium date in my form, the calendar is no longer available for me to select dates. Essentially the users want to use the calendar to select the date but on the form they want to see the format as dd-mmm-yyyy. The users do not want to change their computer settings to the dd-mmm-yyyy and would r...

Date Format #6
I have some external data which is imported into excel and might contain full stops in dates and Excel doesn't seem recognise this very well, is there a way to force Excel to? 01/01/2005 05/01/2005 =NETWORKDAYS(B4,C4) (show as "3", great) 01.01.2005 05.01.2005 =NETWORKDAYS(B5,C5) (show as "#VALUE!") I'd be grateful for any help. Craig Hi Craig try =NETWORKDAYS(SUBSTITUTE(B5,".","/"),SUBSTITUTE(C5,".","/")) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Craig&quo...

help with dates in the past
Hi. I currently have a code that correctly filters the case reviews due for a given month (for my example, it is for March). I want, however, to make a file that filters all of the past due case reviews. Here is my current code: Sub GetOverdueReviews() ' ' GetOverdueReviews Macro ' ' ' Sheets("F Cases").Activate Application.Goto Reference:="R1C1" Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("L1:L5000") 'Header in row Set CopyRange = Range("A1:M500...

Searching for Date Range
I'd like to set up a function to allow the user to search for files based on a due date. I would like it so that the user can search for files due in the following three weeks of the current date. Does anyone know how I would be able to set this up? Use BETWEEN DateAdd("ww", -3, Date) AND Date as the criteria for your date field. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) <jtshockey@gmail.com> wrote in message news:1194892751.925372.73750@e9g2000prf.googlegroups.com... > I'd like to set up a function to allow the user ...

Problem with language and dates in Excel on a Mac
Hi, I've got a problem with excel for Mac - I've tried absolutely everything I can think of and can't seem to fix it... The basic problem is that dates appear in french (specifically, the month name). This is despite the fact that I've set every preference I can find to "English". So 10/07/2006 turns into 10-juil-2006 instead of 10-july-2006. If I look in the language preferences in excel, it's set to English. The language preferences in MaxOSX are also set to English. When I look at the cell format box, it's selected "Date" and the sample form...

Date cell format
Hi experts, I got an Excel sheet with a text column with a series of digits like 08182005 representing date (mmddyyyy). I tried to use cell format to make Excel recognize it as date, but I was not successful. Once Excel recognizes it as date I would like to convert in to the format (DD/MM/YYYY) Thanks, Avi If the values are all 8 digits, select the column, choose Data/Text to Columns..., click Next, Next, and select MDY from the Date dropdown. Click Finish. Format as you like. In article <de25mq$kiq$1@news2.netvision.net.il>, "Avi" <m@m.com> wrote: > Hi ex...

Date #8
How do I auto insert the date on XL worksheet? There seems not to be an icon or drop down. Hold the <Ctrl> key down and press the colon/semicolon key. HTH Jason Atlanta, GA "cesario" wrote: > How do I auto insert the date on XL worksheet? There seems not to be an icon > or drop down. cesario wrote: > How do I auto insert the date on XL worksheet? There seems not to be an icon > or drop down. =today() ...

Passing null to date/time field
I am using MySql and create a new table that has a date/time field. I set it to accept nulls as well. Created a new XSD dataset in ASP.NET but I cannot set the NullValue property to anything but "(Throw Exception)". (The DefaultValue property is <DBNull>.) I then created all of the typical class methods. When I call the Insert method I tried passing system.dbnull.value but it will not allow me to. It says that the type 'system.dbnull' cannot be converted to 'date'. 2 Questions: 1. How can I change the NullValue property in my dataset t...

Stop interpreting Value as date.
Is there any way to stop MS Excel from automaticaly changing a value in a cell such as "02-04" to "2 April". Even if I format the cell as text, the value becomes the date serial value for "02-04". Hi Either format the cell as text before you enter the information, or put a single apostrophe ' before you type it in. Hope this helps. -- Andy. "Rod Jones" <anonymous@discussions.microsoft.com> wrote in message news:13f2701c44495$d0946ae0$a301280a@phx.gbl... > Is there any way to stop MS Excel from automaticaly > changing a valu...

Short date format in strongly typed dataset date column.
Is there a way to save date data in a short format rather than the long form which a strongly typed dataset saves it which looks like this: d3p1:WeekEnding="2003-02-02T00:00:00.0000000-06:00". I would like to shorten it like this: ="2003-02-02" Thanks. -- George Padvorac georgep@dontspamme-nwis.net ...

Create A Bar Graph from A List of Dates
Hi, I have a list of dates spread out over several years and would like to create a bar graph that shows how many of those dates occurred in each month. Can anyone recommend a way to do this? I will have to do this a number of times, so the more automated the process can be, the better. Thanks, George ghadley_00@yahoo.com Hi, You will need to calculate the number of dates in each year. Assuming your dates are in A2:A26 place years you want to count in column C. So for example place 2000 in C2, 2001 in C3 and so on till C9 contains 2007. In D2 enter the following formula =SUMPRODUCT(--(...

Query Date will not span multiple years
Does anyone know why when I query a date range using greater than any date in 2003 and less than any date in 2004 my query's are returned blank. I am performing a querry using a 65 day range and when ever I get to the end of november and I slelsct greater than November and the less than date is Jan 2004, the querry is returned blank. I had this exact same problem this time last year? ...

Comparing Dates using Greater Than and Less than
I am using this formula =IF(12/31/2004<A1:A11<2/1/2005, SUMIF(C1:C11, "CASH",D1:D11),0) to add values from this chart based on their label and the date range under which they fall 1/14/2005 CASH $20.00 1/31/2005 CASH $40.00 2/15/2005 CASH $60.00 2/28/2005 CASH $434.00 3/15/2005 CASH $23.00 3/31/2005 CASH $34.00 4/15/2005 CHECK $100.00 4/29/2005 CHECK $112.00 5/13/2005 CHECK $116.00 5/31/2005 CHECK $8,779.00 12/31/2005 CHECK $787.00 The sytax seems ok, but it always says zero. I think my problem is trying to get the date range right. Can someo...

Working with dates
I have a list of dates in the format dd/mm/yyyy and I want to count the number of dates older than 7 days. I'm trying to use the "countif" function =countif(F7:F80"<today()-7") and if that worked I wouldn't be asking for help. No matter what combinations I try, it always gives a value of zero. I thought that Excel stored dates in the format of a number starting at 1 jan 1900 so I can't see why it won't work. If I take out the minus 7 it still gives a result of zero. Hi Ken! Try: =COUNTIF(F7:F80,">"&TODAY()-7) -- Regards Norman Harker...

midnight message to change date
Hi - If GP is left open through midnight, a message pops up asking if user wants to change date; until you click yes or cancel on the message, you can't access gp and all processes stop running. We have a third party application that integrates with gp and that also stops and that is causing all sorts of issues. So the question is: how can I stop the message from popping up? Thanks, Oumar Hi Oumar, Locate your Dex.ini file in the GP client directory... Add this line to the end of the file and save it. It should provide what you're looking for :-) SuppressChangeDateDialog=TRUE B...

Date calculations
I have a pre-given date and want to know the resulting date after adding a certain number of days in pre-given date. Mathematically it is like: pre-given date + days = resulting date Pregiven date is in one cell of a table on presentation and days and resultings date are in their own cells each. Thanx in anticipation EXCEL 2007 Cell A1:- 01/04/2010 Cell B1:- 10 Cell C1:- =(A1+B1) This gives the answer 11/04/2010 in cell C1. If my comments have helped please hit Yes. Thanks. "kharal" wrote: > I have a pre-given ...

Date 05-25-10
I have a date field [fstrlsedt] and a number field [prd]. The number field is actually refering to number of months. How to put an expression to find a new date by adding [fstrlsedt] with the number of months [prd] zyus wrote: >I have a date field [fstrlsedt] and a number field [prd]. > >The number field is actually refering to number of months. > >How to put an expression to find a new date by adding [fstrlsedt] with the >number of months [prd] Hmm... the DateAdd function sounds awfully promising!!! Create an unbound control and use something like Da...

2005 dates
I am working on a reservation list for next year..... how can I get excel to enter 2005 when I put in dates and NOT 2004....... thanks Dave Type it in, enter for xmas day 25/12/5 and it will take it as next year "Dave" <post@site.com> wrote in message news:%23rZKnwewEHA.1260@TK2MSFTNGP12.phx.gbl... > I am working on a reservation list for next year..... > how can I get excel to enter 2005 when I put in dates and NOT 2004....... > thanks > > Dave > > You could right click sheet tab>view code>insert this. Now, when you enter a date below row 4 a...

Export text with current date
hi, I want to export a few queries to text files. I am current using "transfer text" option through macros. If we provide the file name and the path the files are exported to the path. How do we add the current date at the end of each file. eg. abc is the query name & i want the query to be exported as data_25122007. Currently i am able to get the file name as data_. the date should be dynamic (ie) should give the current date at the end of the file. Any ideas, Pls help. Hi, also from Australia is an answer for you strFileName = "queryName" & "_" ...

Dates and times
I need to be able to calculate down to the # of days and hours between 2 dates. For instance I receive document at 11:50 a.m. on 10/1/03 and it was processed at 6:00 p.M. on 10/3/03. I want a column that will tell me the difference in either days and hours or just hours('cause we know 24 hours =1 day etc..). How do I do this? Just subtract =A2-A1 format as [hh] -- Regards, Peo Sjoblom "Krandall" <Krandall@technisource.com> wrote in message news:060801c38e6f$6ba18f50$a001280a@phx.gbl... > I need to be able to calculate down to the # of days and > hours...