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 |

12/17/2009 9:18:40 AM

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

12/17/2009 11:34:01 AM

another quetion with my homework. Use the DSUM function to calculate the total number of vacation days remaining. I put in this: =DSUM('Employee Data'!E6:E109,"17",D4) You do this for days remaining. I get the E6:E109 from the Employee Data sheet that Is titled Remaining Vacation Leave. I get the D4 from my leave summary sheet that shows the total days as 612. You do this for 17 day vac, 12 days of vacation,7 and then 0. I get an error #VALUE Not sure what to do or where to go with this. -- Too old to be in college It is a pretty good explanation in help for the ...

We are using Canadian Payroll and currently the system only reflects YTD values for those pay codes that are utilized during a given pay period. Any pay code that may have been used in prior pay periods is not shown. This is a real problem as year end is approaching and employees want to be able to verify T4 values against their last pay stub for the year. I realize that the US version has the capability to do this and suggest the same be done for the Canadian version too. Thank you. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions...

I want to sort my tasks so that the most pressing items are displayed at the top of the list, i.e. by ascending due date. I have setup my tasks to sort by due date in ascending order, but the problem I have is that items with no due date are displayed above those with a due date. How do I sort by due date, in ascending order, with items with due dates displayed above those without? Thanks in advance Jon This is the same question that scorpius asked. I believe that there is no way to do this. "Jonathan Longthorp" wrote: > I want to sort my tasks so that the most pres...

Hi! - I was hoping someone could help me please. Here's what I have. A worksheet that has several dates in a row. D1:T1, Of that range, I have conditional formatting set to highlight a date(s) either = to or within the next 7 days. I need to be able to pull the date next closest or equal to today. Can anyone help me out? Any help would be greatly appriecated. Shaun > I was hoping someone could help me please. Here's what I have. A > worksheet that has several dates in a row. D1:T1, Of that range, I > have conditional formatting set to highlight a date(s) either = to or...

Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I have found a "bug" in the new excel which means if I copy and paste a date or date range, from an earlier version of Excel (ie an older spreadsheet), the date in the 2008/new excel changes to a date 4 years and 1 day more than the original date. Even if I try to link the cells between new and old excel, the returning date value is incorrect. Does anyone know how to fix this???? HELP!!! Sorry to burst your bubble :-) but you haven't discovered a bug. What you've discovered is that th...

I have a speed sheet that calculates the number of days required to get a project completed. in a feild it calculated the estimated completion date based on the new start date each morning. but i need it to show Monday if thet date is Saturday or Sunday. On 22 jun, 15:16, ray <r...@discussions.microsoft.com> wrote: > I have a speed sheet that calculates the number of days required to get a > project completed. > in a feild it calculated the estimated completion date based on the new > start date each morning. > but i need it to show Monday if thet date is Saturday or Su...

When I type '11/12' for example in Excel 2003 it automatically changes it to '12-Nov'. How can I remove this function? I tried endlessly to change thing in 'options' but nothing seemed to work! Thanks for any help Format the cell for text. (This doesn't fix cells you've already typed in) It always will do that as it sees a date, try '11/12 the apostrophe will show in the formula bar but not in the cell, the ' converts it to text, Regards, Alan. "TKassis" <TKassis@discussions.microsoft.com> wrote in message news:EF7291DB-615E-4D64...

i am in need of a formula that will compare the current date with th date in another cell. if the date in the other cell is past th current date, i want that cell to be highlighted in Red. For example if the date in the cell is June 10, 2005 and today's date is June 22 2005, i want excel to turn that June 10, 2005 cell Red. Also i possible can i tell excel to turn the cell Yellow if that date HAS NO passed but is approaching (say within 30 days)? Can someone pleas tell me 1. if any of this is possible, and 2. what formulas i need t accomplish this. Thanks -- khaji0 ------------------...

Hi. I have a date problem. I have got a workbook with a date column from a friend. When I copy a cell from the date colomn and past it in another workbook I get a different date. When I format it in text format, there are the same numbers. For example: I have a cell with format Date (15-3 05) and the cell value 27-5 05. (The value for this cell in text format is 37037). I copy this cell and past it in a New Workbook, and the date is 26-5 01. I look at the format and it�s the same. And the value in text format is also the same, 37037. But when I past it in the same workbook I copy it from, th...

Hello, I have a query where the completition is like 3/4/10 6:51AM, the second shift is working from 7:00PM~7:00AM. So I need to get a day before, in some case where the time is 00:00Am~7:00AM, please help me on that, Completed_Date ShiftData 3/4/10 6:51 PM 3/4/2010 OK 3/4/10 6:51 AM 3/3/2010 Need a day before 3/5/10 12:51 AM 3/4/2010 Need a day before Thanks LD -- Lorenzo Díaz Cad Technician IIf(TimeValue([Completed_Date]) < #07:00:00#, DateAdd("d", -1, DateValue([Completed_Date]), DateValue([Completed_Date])) -- Doug Steele, Microsoft Access MV...

I have need to show dates as follows: 1/1/05 = 1st Saturday of January...any ideas out there? You can't format the dates as 2nd Sunday of January 2005 but there are ways of using formulas to get nth day etc http://www.cpearson.com/excel/DateTimeWS.htm#NthDoW Regards, Peo Sjoblom "debra adams" wrote: > I have need to show dates as follows: 1/1/05 = 1st Saturday of January...any > ideas out there? As Peo mentioned, I doubt you can format the dates as such. You can, however, convert it to a text string in another cell. Assuming your dates are in column A, star...

hello, How do I add 1 calendar month to a date, regardless of the number of days in the month? Thanks KK Hi Maybe this : =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) Change A1 for your needs. HTH John "KRK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message news:e6a1DA46KHA.1888@TK2MSFTNGP05.phx.gbl... > hello, > > How do I add 1 calendar month to a date, regardless of the number of days in > the month? > > Thanks > > KK "KRK" <trebor@yeleek.nospam.freeserve.co.uk> wrote: > How do I add 1 calendar mont...

Hi, can anyone help please ? running a DSUM fomula below =+DSUM(maindata,"it_cost",H1:I2)/100 H1:I2 holds a conditional name for a sales rep to extract his sales from the data on a specified date. The problem I have is the Quantity needs multiplying by the it_cost on each row before the DSUM result is obtained. i.e. total sum of all reps sales on a specified date Qty x cost = x do I extend the DSUM formula to get the required answer? thanks -- Vass ...

Below are my 2 tables:- Table1 ItemID (primary key) Days Table 2 Serial number(primary key) ItemID dateExpired I need the dateExpired to be auto calculated when I enter the ItemID. The dateExpired is today's date + the days in the table 1...May I know the way? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1 It would be a good idea to create an index for the ItemID field in Table 2 You did not say whether there is a form involved of if you are doing recordset processing or if this is being done in a query. Where you...

How do I format a date so that when I type 8-4, it gives Aug-04, or when I type 5-3 it gives May-03. This is month and year. I can use a work-around and custom format mm/dd which gives me in the printed form what I want, but when you click in the cell it will shows 8/4/2005 in the formula bar. This is a month and year format and I want it to reflect nothing more in the formula bar. If 8-4 is August 2004, I want it to give me 8/04 in the cell, but something in the formula bar showing that this is month and year, i.e. 8/2004. Hope I have explained myself. If I format mm/yy, then I h...

I have a workbook with two sheets. One of them has some data and the other has a ODBC connection to an Oracle database. I use the following array formula to get some values = INDEX('Cementaciónes Ya Cargadas'!$D$2:$D$76,MATCH(1,('Cementaciónes Ya Cargadas'!$A$2:$A$76='Cementación Secundaria'!$B8)*('Cementaciónes Ya Cargadas'!$B$2:$B$76='Cementación Secundaria'!$E8)*('Cementaciónes Ya Cargadas'!$C$2:$C$76='Cementación Secundaria'!$I8),0)) The problem resides in that one of the values to compare, s...

Whats wrong with this code YearCabinsTurnover = DSum("[SumOfSubTotal]", "Query8", "[Year]=" & NowYear And "[OrderType]= '2'") Thanks SImon On Wed, 24 Feb 2010 13:45:37 -0800 (PST), Simon wrote: > Whats wrong with this code > > YearCabinsTurnover = DSum("[SumOfSubTotal]", "Query8", "[Year]=" & > NowYear And "[OrderType]= '2'") > > Thanks > > SImon What is NowYear? A Control name on a form? I'll assume it will have a Number value, i.e....

I'm using a simple dsum to collapse and summarize records. My criteria is a text field. My target or search database column is a text field. DSUM returns invalid summations, because it appears to find text matches for any target record that has common characters starting from the left most or first cahracter. ie.. DSUM return values where Criteria = bob target = bob value to return = 1 target = bobby value to return = 1 target = bobxxxx value = 1 dsum returns 3. this is incorrect, as i only want the records for "bob". i do not want the records for "...

I have a report that is sorted by date. I have formatted the dates with a "ww" format that groups and creates a header by the particular week number. For every group or week number I would like to create a header that states the week number and the date of the monday of that week. Example: Week 2 Data1 Data2 1/8/08 Data1 Data2 1/9/08 Should be: Week 2 Beginning Monday 1/7/08 Data1 Data2 1/8/08 Data1 Data2 1/9/08 Any thoughts, ideas, or suggestions would be appreciated. If the question is unclear, please let me kno...

I have been working with an Excel sheet that I need to enter 19th century dates. When you enter a date as an example: 3/4/1897, the cell will not format correctly. When I try to import this data to Access I get errors for dates that are in the 19th century. Any ideas? On Sat, 9 Aug 2003 18:50:03 -0700, "Jerry Malone" <jerry.malone@mchsi.com> wrote: >I have been working with an Excel sheet that I need to >enter 19th century dates. When you enter a date as an >example: 3/4/1897, the cell will not format correctly. Excel's date numbering system starts from 1/...

I plan our events using Access. At the moment when I'm given the dates for an event e.g. 1st July to 17th July I have to manually create a new record for each day. Is there any way in an update query I could type in my constants (event type, event name, event location) and the start date and the end date and create records for all of the days between the start and end date? If there is, I have no idea what to set the "update to" and "criteria" to? Any help would be greatly appreciated. JAMES On Jul 26, 11:12 am, James Frater <JamesFra...@discussions.micros...

Hi All How to using formula to "Number of Date between two date but not include Saturday/Sunday" ? e.g. 2010/12/03 to 2010/12/07 return 3 days. 2010/12/04 to 2010/12/07 return 2 days. moonhkt Check out the NETWORKDAYS function in Excel Help. If you are using Excel 2003 or earlier you will need to instal the Analysis Toolpak. Hope this helps. Pete On Dec 13, 9:08=A0am, moonhkt <moon...@gmail.com> wrote: > Hi All > > How to using formula to "Number of Date between two date but not > include Saturday/Sunday" ? > > e.g. > 2010/12/03 to 2010/12/...

Could use some help with the following: I created a spreadsheet to track payments made to contractors. In this spreadsheet I have a simple formula that tells when the payment has to be made based on the date it was rec'd from the contractor and adding on the payment terms. Now what I have to do is add in some date constraints. Our paying office only processes payments between certain windows during the month. Lets say that for this month the payment window closes Jan 27th to Feb 2nd. What I need to do is if after someone enters the date the payment is rec'd and the 14 days is ...

Hi, I am trying to get results in a query using the between two dates. (ie between 1/1/2008 and 1/31/2008) The date is formated in date plus time. (ie 1/31/2008 10:27:16 AM) Everytime I try to get the dates for Jan for some reason 1/31/2008 will not come up in the results. I have 5 fields that need to show but are not. In the past I have had not problem but since I got my new computer I can not get the results. Any help is greatly appreciated. I have Access 2002. The problem is that 1/31/2008 10:27:16 AM is after 1/31/2008 00:00:00. (midnight). All dateTime fields in Access h...

I have a list of dates with various numbers listed as the day (03/25/2009, 04/05/2010, etc.) I need a formula to change all of the dates to display 01 as the day (03/01/2009, 04/01/2010, etc). Thanks! -- LL28 First date in A1 (03/25/2009) In B1 =DATE(YEAR(A1),MONTH(A1),1) will display 01/05/2009 best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "LovingLife28" <LovingLife28@discussions.microsoft.com> wrote in message news:FDFA5015-6DF0-4374-842F-C8DA14E9952F@microsoft.com... > I have a list of dates with various number...