How to do a Date Macro In Excel

[llolsen]

I have a worksheet with payment dates per employee - I need a Macro that at sum the amount per person with a date range of month (first two digits of date field).

can anyone help?

thanks

 

[SkipVought]

hi?

Please explain what you need WITH an appropriate example that clearly illustrates your issues.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[llolsen]

For example, the below is a copy of data from worksheet for one person:
09/17/10 3,230.77
10/01/10 3,230.77
10/15/10 3,424.62
10/29/10 3,553.85
11/01/10 1,925.00
11/15/10 3,850.00
11/30/10 0
11/30/10 4,029.67
12/15/10 4,235.00
12/30/10 4,235.00

I need sub-total macro that will sum amounts earned per person with a date range. So if I a sub-total for date range 10/12/10 thru 11/3 (it would give me sum of payments dated 10/29, 11/1 only. Other date range example is 9/29 thru 10/29.

thanks!

 

[SkipVought]

Please post data that includes the 'person.'

Surely you do not have separate sheets or tables for each person.

Does your data not also have headings?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[llolsen]

First column represent EE#
Pers.No. Pmt date Amount
10000472 09/17/10 3,230.77
10000472 10/01/10 3,230.77
10000472 10/15/10 3,424.62
10000472 10/29/10 3,553.85
10000472 11/01/10 1,925.00
10000472 11/15/10 3,850.00
10000472 11/30/10 0
10000472 11/30/10 4,029.67
10000472 12/15/10 4,235.00
10000472 12/30/10 4,235.00
10000478 09/17/10 2,500.00
10000478 10/01/10 2,500.00
10000478 10/15/10 2,697.85
10000478 10/29/10 2,740.00
10000478 10/29/10 0
10000478 11/01/10 1,929.61
10000478 11/01/10 0
10000478 11/15/10 2,968.33
10000478 11/15/10 0
10000478 11/30/10 2,968.33
10000478 11/30/10 0
10000478 12/15/10 2,968.33
10000478 12/15/10 0
10000478 12/30/10 2,968.33
10000478 12/30/10 0
10000487 09/17/10 4,884.62
10000487 10/01/10 4,884.62
10000487 10/15/10 5,060.46
10000487 10/29/10 5,177.69
10000487 11/01/10 2,804.59
10000487 11/15/10 6,655.90
10000487 11/30/10 5,609.17
10000487 12/15/10 0
10000487 12/15/10 5,721.35
10000487 12/30/10 6,170.09
10000490 09/17/10 2,818.79
10000490 09/24/10 3,721.61
10000490 10/22/10 2,977.97
10000490 10/29/10 2,997.78
10000490 11/05/10 4,664.58
10000490 11/12/10 742.39
10000490 12/03/10 3,017.59
10000490 12/10/10 4,577.05
10000490 12/17/10 3,037.40

Thank you

 

[SkipVought]

So if I a sub-total for date range 10/12/10 thru 11/3 (it would give me sum of payments dated 10/29, 11/1 only. Other date range example is 9/29 thru 10/29.

How is the date range determined? For instance, from your statement above, 10/12/2010 thru 11/3/2010 is not a full month, while 9/29/2010 thru 10/29/2010 is, AND 10/12/2010 is a TUESDAY while 9/29/2010 is a WEDNESDAY.

Please state your requirements clearly, concisely and completly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Don't know how much automation you need, but this is just a simple PivotTable report using the 9/29 to 10/29 2010 criteria. Results in less than 60 seconds...
[tt]
Sum of Amount Pmt date
Pers.No. 10/1/2010 10/15/2010 10/22/2010 10/29/2010 Grand Total
10000472 $3,230.77 $3,424.62 $3,553.85 $10,209.24
10000478 $2,500.00 $2,697.85 $2,740.00 $7,937.85
10000487 $4,884.62 $5,060.46 $5,177.69 $15,122.77
10000490 $2,977.97 $2,997.78 $5,975.75
Grand Total $10,615.39 $11,182.93 $2,977.97 $14,469.32 $39,245.61
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[llolsen]

Date range is on a per employee basis. It is the days they were in a specific country. I know I would have to modify the statement for the different dates but it would be faster than me clicking on = and capturing each payment for the date range per person.

 

[SkipVought]

So do you have a table of Start & End dates for each EE#?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

for instance, I have your table in columns A-C

In column E-H I have this table of EE#, Start, End and TotPmt
[tt]
EE# Start End TotPmt
10000472 9/29/2010 10/29/2010 $10,209.24
10000478 9/29/2010 10/29/2010 $7,937.85
10000487 9/29/2010 10/29/2010 $15,122.77
10000490 9/29/2010 10/29/2010 $5,975.75
[/tt]
Here's the formula, using Named Ranges in your table...
[tt]
H2: =SUMPRODUCT((Pers.No.=E2)*(Pmt_date>=F2)*(Pmt_date<=G2)*(Amount))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[llolsen]

YOU ROCK! Thank you so much!

 

[llolsen]

Skip

Not sure what I did wrong. Please see attached.

 

[SkipVought]

TWO separate tables. That is not what you tried!

1. Your 3-column table you posted, with NAMED RANGES based on the headings in row 1.

2. A unique list of your EE#s TOTALLY separate from your 3-column table. Put it on another sheet if you want!

In the columns adjacent to you EE# List, START & END columns for the start & end dates for each employee, like the sample I posted with your 4 EE#s. The NEXT COLUMN will have your formula. Naturally each criteria reference will have to correspond to the location of this table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}