Free Microsoft Excel 2013 Quick Reference

DSUM with dates??

I have been creating a spreadsheet in Excel to track warranty sales for
my business. On this sheet i need to keep track of the Date, Associate
who sold it (by employee number), the ammount sold, Month to date
sales, and average dollar ammount.

Each day in the table consists of 2 columns. One with The date as the
title, and the other as "associate" as the title. In order to figure
out what the month to date, and average sales are i need excel tosearch
this table by an associate number (off of a seperate report sheet) then
have it adjust to add the totals in the column to the left, the date
column, which actually shows the sales amount. I have been trying with
multiple DSUM functions, and even with long sumif functions without
luck, does anyone know how i can accomplish this?

It is a rather long table, probably 40-50 columns. and set up similar
to this

7/21 associate 7/22 associate 7/23 associate
15.99 46465 4.99 484878 5.99 798765

22.99 46565 8.99 65686 4.99 65686

and ultimatly i need to be able to locate an associate (65686) and sum
his totals for the entire month (8.99+4.99) and show this on the report
page.


Post your answer or comment

comments powered by Disqus
I have a table with dates in one column and am trying to sum the values between dates inserted by the user.

Criteria is:

Brand Item qty TotalFullPrice NetEach TotalNetSale Date Type

In the date field, is it possible to enter a DSUM formula concatenating the start and end dates inserted by the user????

I had the same problem as Michel, and found your post most helpful. Where did
you find this information? I could not find it in Excel help, nor could I
find it in the five or six Excel manuals that I consulted.
Thank you.

"Aladin Akyurek" wrote:

> Let E1:E2 be the criteria range with:
>
> E1 housing Date as the relevant label.
>
> In E2 enter:
>
> =">"&J1
>
> where J1 houses a date that you want to invoke as the date criterion, which
> you can replace with any date at will..
>
> A DSum formula like:
>
> =DSUM(A1:B15,2,E1:E2)
>
> would calculate the desired sum.
>
> Is this what you wanted to do?
>
> "Michel from Belgium" in message ...
> > Hi everybody,
> >
> > I am working with Excel XP.
> >
> > I have a database with 2000 records and dbase formulas (mainly dsum) to
> > calculate
> > results in date ranges. The problem I have comes from the date criteria.
> As
> > I neeed to change regularely the dates, I tried to insert a formula in the
> > date criterion field, but without any success . The formula should look
> like
> > : > 8/1/2003 but I cannot make it work as > is not a numerical factor.
> >
> > Could somebody help me ?
> > Thanks in advance
> >
> > Michel from Belgium
> >
> >
>
>
>

I am using a spreadsheet to track & monitor our household expenses. On one sheet have columns for date (as 22/09/2010), category, income & expense. On the second sheet, I have a column for each category of income/expense, & rows for Jan, Feb, Mar, etc.

In the second sheet I have been using SUM & IF statements to calculate where the category in the first sheet is ‘Phone’, if it is greater than 01 Mar & if it is less than 31 Mar, then add together all corresponding values in the expense column. All these calculations make the spreadsheet wrok very slowly when I change something, & I was wondering if the DSUM formula would work for this? How do I get DSUM to use a range of dates (1 – 31 Mar) as a criteria?

An example of the formula I am currently using is: {=SUM(IF(Sheet1!B1:Sheet1!B5000=A14,IF(Sheet1!A1:Sheet1!A5000>=DATE(2010,3,1),IF(Sheet1!A1:Sheet1!A5 000<=DATE(2010,3,31),Sheet1!E1:Sheet1!E5000,""))))}

I have a large database with date and timestamp. Trying to find the best formula that would count each line within a time zone. Example below.

8/1/11 8:48 AM 8/1/11 8:48 AM 8/1/11 8:03 PM 8/1/11 8:03 PM 8/2/11 2:22 PM 8/2/11 2:22 PM 8/2/11 7:48 PM 8/2/11 7:48 PM 8/3/11 8:14 AM 8/3/11 8:14 AM 8/3/11 8:57 PM 8/3/11 8:57 PM This may be a two step formula but need to know count of those between 8-9 AM and 8-9PM, 2-3AM and 2-3PM, etc.

Update asset list with date and room

Hiya

I am very new to Excel macros, and i have an asset list in excel (see attached example, sheet 1).

Asset numbers are incremental and unique.

Every year we get a list of asset numbers that have been found (see sheet 2) that are located in a particular room on a particular date.

I need to update the main asset list with the date (Date checked) and Room for each asset found on the list. I could also do with a prompt each time an asset is to be updated saying "Update or Skip".

Can anyone help me please...i really dont know where to start...I've tried recording macros but this doesn't seem to work, and i think i need to set a room and date constant..

Please help!! Kim

To Excel Forum helpers,

I need help in working with dates. I have dates entered in two columns in a general format as follows.

ACT_DT APT_DT

81603 8162003
81603 8162003
81603 8172003
81603 8182003
81603 8192003
81603 8192003
81703 8212003
81703 8212003
81703 8232003
81703 8242003
81703 8242003
81703 8252003
81703 8262003

and so on for a total of 1500 values.

What I want to do with the data is to convert the data in the two columns above representing dates into the date format and find the difference in days between ACT_DT and APT_DT. When I tried to format 81603 into a date format the date showed up as 6/2/23 and not as 08/16/03. Can someone please help me with this problem?

Thank you very much

Angela

How can I specify up to 3 criteria in a nested IF statement with dates?

I read somewhere that an * asterisk can be used to say "and". Questions:

1. What sequence do I put this in? What question must be asked first before the others?

2. How can I add a 3rd criteria with another * asterisk?

Sequence and syntax is where I'm baffled.

What I'm trying to accomplish is that I have to verify the following (column names in "quotes"):

- "Status" of Client
- "Document Type" received
- "Current Contract End Date"

Ultimately, I need to make a 3-column pivot table that will show:

1. Active Clients with Contracts Ending in 2008
2. Active Clients with Contracts Ending 1/1/09 or greater
3. Active Clients Transferring to our resource company (for any dates 1/1/08 or greater - these we track separately)

Here is what I have so far. Can you help with the correct syntax? Not means they have to be filtered out, and will not be included in the pivot table. (We are not tracking clients whose contracts ended before 1/1/08.)

=IF((Q2>DATEVALUE("12/31/2008"))*(H2"M"),"Mid-Contract Terms",IF((Q2>DATEVALUE("12/31/2007"))*(H2"M"),"2008 Terms",IF((Q2>DATEVALUE("12/31/2008"))*(H2"M"),"2009 Terms",IF((H2="M"),"Resource Company","Not"))))

Thank you very much.

Is it possible to concatenate with date in mmddyyy format?

I only get the long format 39710.8391841435.xls
Inventory Control-Backup39710.8339836806.xls

Inventory Control-Backup39710.8339836806.xls

Thanks,

Craig

Hi all.

I am having a problems with dates comparison. I cant seem to get it do what I want.

This is the code:
Code:
 
Private Sub txtEndDate_LostFocus()
txtEndDate.Value = CheckDate(txtEndDate.Value)
 
MsgBox txtEndDate.Value < txtStartDate.Value
If txtEndDate.Value < txtStartDate.Value Then
    MsgBox "Please enter an end date greater than the start date"
    txtEndDate.Value = "dd/mm/yyyy"
End If
 
End Sub
----------------------------------------
Private Function CheckDate(strDate As String) As String
On Error GoTo ErrorHandler
 
If strDate = "" Then
    CheckDate = "dd/mm/yyyy"
ElseIf InStr(1, strDate, "/")  3 _
    Or InStr(4, strDate, "/")  6 _
    Or Len(strDate)  10 Then
        MsgBox "Please enter a date in the correct format"
        CheckDate = "dd/mm/yyyy"
Else
    CheckDate = strDate
End If
 
Exit Function
ErrorHandler:
    Err.Raise vbObjectError
 
End Function
What happens is when I enter a start date of 15/05/2008 and an end date of 02/06/2008, the code thinks that the end date is before the start date. I have a feeling this has to do with the formatting of the dd/mm/2008 in my excel setup, but I am not sure. Can anyone please help me out?
Thanks.

HI

I need to copy whole row by compairing it with date

i can use vlookup for it will only get 1 cell value

example
Date t1 t2 t3 t4 t5
11/04/2008 45 56 57 48 49

Sheet where info will be pasted

Date c1 c2 c3 c4 c5
11/04/2008

please help

Is it possible that each time I save a file it saves the filename along with date and time as the version of the file in a specific path?

I have this problem in formulating formulas. The background is i have a table where the parts are installed initially in a certain date. The table looks like this.

Column A - Represents the place where the part is installed
1 A1
2 A2
3 A3
4 A4
5 A5
6 A6
7 and so on

Column B - Represents the parts installed corresponding to the the place at column A.

1 a
2 b
3 c
4 d
5 e
6 f
7 and so on

column c - represents the spare parts available

1 u
2 v
3 w
4 x
5 y

Now the parts in column B will be replaced when it will need maintenance and will be pulled out and will be replaced with a spare in column c. So i make a table for replacement starting at column d (with date entries), column e (with place of installation entry), and column f (for parts entry) just for example purposes.

column d-e-f

jan 5, 2008-A2-w
jan 10, 2008-A1-y
jan 28, 2008-A5-u
feb 20, 2008-A6-w
etc...

At cell g1 is the look up value for installed part. i.e the letter alphabets as exampled.

At g2 is the formula that first looks up at column "f" to look for the part typed @ g1. if it finds the part entry it will simply return the latest entry value for the place where the part is installed (i.e. if i type w in g1 it will return a value of A6).

if the formula does not find the part there at column "f" it will look at column b, and returns the value of the corresponding value at column A (i.e. if i enter c in g1, it will return a value of A3).

if the formula does not find the part in either column "f" or Column b, then it will look at column c, and returns a common value of "Spare" if it finds the part there (i.e. if i type v in g1, it will return a value "spare" in g2), if not it will return a value of "Non-existing Part" string.

hope can help me. thanks in advance...

Hi All,

I am currently coding up a VBA macro that will counts up the number of occurrences of a particular event for a particular date. The problem is that it keeps returning zero as the result. I thought it might have been a data type mismatch issue as the cell format is a date in the mm/dd/yyyy format but the data is stored as a string so that I can easily manipulate it with the built in VBA functions. I tried changing the data type but still received zero as the result. 'a' and 'c' refer to cell ranges, criteria is a string of characters and criteria2 is a string that pulls in a date in the format of mm/dd/yyyy. I know this code works when I don't use dates but I can't figure out why it doesn't work with dates. Any help is greatly appreciated.

Thanks,

Sally

Code:
criteria = Application.Workbooks(FileName).Worksheets("Day_Counts").Range("B1").offset(0, column).Value
            
criteria2 =  Application.Workbooks(FileName).Worksheets("Day_Counts").Range("A2").offset(row, 0).Value
criteria2 = Format(criteria2, "mm/dd/yyyy")

a = Application.Workbooks(FileName).Worksheets(FileNameNE).Range("G2:G" & EOF).Address

c = Application.Workbooks(FileName).Worksheets(FileNameNE).Range("D2:D" & EOF).Address

result = Evaluate("SUMPRODUCT((" & a & "=" & Chr(34) & criteria & Chr(34) & ")*(" & c & "=" & Chr(34) & criteria2 & Chr(34) &
"))")


I am trying to set up a spreadsheet with date headers and/or side labels,
one week apart.
I have tried without success. Any help gratefully received.

1/1/2006 - 8/1/2006 - 15/1/2006 etc.

Frank Taylor
Dell Dimension 8400
4 Gig RAM
1.3 GigHz Pentium CPU
Multithreaded
Windows XP Pro - SR2
Canon S530D
160 Gig Hard Drive.

I wonder if anyone can help me with this as it's driving me mad.

I have Excel 2003 running under Vista Home Premium and everything was
fine until a few days ago. I have spreadsheets with dates entered in
the following format:

Date: 14-Mar-01 - so when I enter 16/11/07 it appears in the cell as
16-Nov-07. The locale is: English (United Kingdom)

A couple of days ago I installed a program which needs to change my
regional settings to US while it is running and then changes them back
when I exit. Everything works fine and all the regional settings are
changed back to UK after closing this software.

However, in Excel, all new sheets now open up with the date set to
English (US). Even if I format the cell to English (UK), I canot enter
the dates as I did before. If I now enter 16/11/07 it just stays in
this format. I have to enter the date in US format, 11/16/07 before it
will change to 16-Nov-07, even thought the cell is set to UK format.

I am sure the software I installed has changed some variable but how
do I change it back?

Thank you.

Hi everyone.
I have a worksheet with two columns. The first with date and the other with
text formated cells.
When I enter in the first one data with the year < 1900 the format never
assumes the right date format
Can anyone provide me with some explanation about this?
Regs
Alexandre Duarte

Is it possible to count N of cells that contain dates? not a specific
date, but any date? e.g. in one column there are cells with dates and
abbreviations, and those with dates need to be counted.

thanks for help!

-Evgenia

--
Evgenia
------------------------------------------------------------------------
Evgenia's Profile: http://www.excelforum.com/member.php...o&userid=28946
View this thread: http://www.excelforum.com/showthread...hreadid=486802

how can i calculate with dates before 1-1-1900? i also can't change the
property of the cell when using dates before 1-1-1900.

I am trying to combine an if function with dates:

my formula reads: =IF(C2

Hello

I have database which includes also dates in UK format (dd.mm.yyyy). I
am able to display everything using UserForm with TextBoxes, but dates
are displayed in US format (mm/dd/yyyy).
I am able to change that when I leave the TextBox where is the date
using the following procedure

Private Sub TextBox13_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date
dt = TextBox13.Text
TextBox13.Text = Format(dt, "dd.mm.yyyy")
End Sub

but the question is - is there some way to display date in UK format
immediately when UserForm with the TextBox including date is
displayed? (after UserForm.Show)? so there is no need to go into the
TextBox with date?

Thanks for the comments
Regards
Johny

i am trying to fill a column with dates in descending order from dec. 31,2010
to jan.1 2010 and i having a difficult time trying to accomplish this

I’m trying to use the conditional formatting, traffic light feature
with dates and am finding the new Edit Formula Rule app to be rather
limiting. Hopefully it’s just my ignorance.

Cell E1 contains current date (updated when the workbook is opened)
several other cells have fixed expiration dates. In the expiration
date cells I want traffic lights indicating: red once the date matches
current, yellow seven days in advance, and green anytime the date in
the cell is greater than 7 days beyond the current date.

I can get red and green to work but getting yellow is a mystery.

Green value is set to “E1” and Type set to Number

Yellow says “when < formula and” then there is an option to select >
or “=>” and enter a value. In the value I have tried “=E1+7 just “=7”
etc but all I can accomplish is that yellow never works or will always
work when the date is exceeded with red never working.

I assume that yellows formula in “when < formula and” is referring to
the “value” for green even if number is selected for green instead of
“formula”. Makes me think that I could never create the condition I
want.

If I sound confused it’s because I am! The help feature does not
explain the feature that I can find
Any help will be appreciated.
Robert

I have calls log in a sheet(All Call Details) with columns as follows:
Type,Date,Time,Number,"Duration/Volume",Amount,D,OperatorName,Day
respectively from A thru J.

In another sheet (Bill Summaries) i have two cells H5 and H6 with starting
and ending dates of this billing period. Now i want to retrieve all the call
charges with type "OG Local" and "OG National" within these dates. I used the
following function in one cell in Bill Summaries sheet.

=SUMIFS('All Call Details'!F:F,'All Call Details'!A:A,"OG*",'All Call
Details'!B:B,">'Bill Summaries'!H5",'All Call Details'!B:B,"

How do I filter using dates? I am trying to filter out dates for each month,
example I want to collect all of the entries with dates in September. I am
using Office 2003.


No luck finding an answer? You could always try Google.