Counting based on combined text and date criteria

[SPUT]

I am trying to use the countif function to count the dates after the current date. When I use " <Today()" for the criteria it gives me zero. When I type in the date it gives me the number I want. Can anyone tell me how to use the Today() to make it work

[ExcelTip]

Problem:

In range D12:E16 are file names and their corresponding creation date.
How could we count the number of Excel files (\"\".xls\"\" ending) created in April?

Solution:

Use the MONTH and RIGHT functions, in this SUMPRODUCT formula:
=SUMPRODUCT((MONTH(B2:B6)=C2)*(RIGHT(A2:A6,3)=C3))


File Name_____________Creation Date____Criteria
Workbook1.xls_________02/03/2005_______4
Document1.doc_________05/01/2005_______xls
Presentation1.ppt_____09/09/2004
Workbook2.xls_________10/04/2005
Document2.doc_________08/11/2004

Result:___1

[Julie Allen]

Is it possible to have a Budget refering to a calendar.
I need to calculate the days left from a particuar Start and End Date, then divide the monies over the rest of the current month and future months.
Considering the different amount of days in each mth
Example:
Calendar reference
Budget Amount start end days left
$10,000,000.00 21.05.05 31.05.05 10 = $1,000,000.00???

Currently showing 1 mth only and this will need to be adjusted several years ahead
Can anyone help??

[Alan]

Hi Julie,

Is it possible to have a Budget refering to a calendar.
I need to calculate the days left from a particuar Start and End Date, then divide the monies over the rest of the current month and future months.
Considering the different amount of days in each mth
Example:
Calendar reference
Budget Amount start end days left
$10,000,000.00 21.05.05 31.05.05 10 = $1,000,000.00???

Currently showing 1 mth only and this will need to be adjusted several years ahead
Can anyone help??

If you have the your data as follows:

A1 = 10,000,000

B1 = 21 May 2005

C1 = 31 May 2005


E1 = C1 - B1 = 10

F1 = A1/E1 = 1,000,000


HTH but if not, do post back.

Alan.

[jallen]

Hi Alan,

I have this formula figured already. Thanks
The problem is the need to adjust these date ranges to month/years ahead, and still be able to spread the budget amount across. Keeping in mind that each mth has different amount of days.
I know this can be set up manually, but I was hoping that there is a way to link MS Calendar software to MS Excel?
Thanks,
Julie

[Alan]

Hi Julie,

Hi Alan,

I have this formula figured already. Thanks
The problem is the need to adjust these date ranges to month/years ahead, and still be able to spread the budget amount across. Keeping in mind that each mth has different amount of days.
I know this can be set up manually, but I was hoping that there is a way to link MS Calendar software to MS Excel?
Thanks,
Julie

Just extend the dates across the range in question (month end dates for example), then place another formula below or beside that to calculate the number of days between each month end. Finally, apportion the balance across the time period in proportion to the days.

I cannot really be more specific without a specific example, but each step should be fairly trivial if you do them one at a time.

HTH,

Alan.

[felixg]

hey alan,

How you doing.

i have this problem, when u try to rest tow cell from excel, excel display this:

a1= 4/2/05 (dd/mm/yy)

a2= 2 (numeric)

a3= a1-a3 so

a3 should be: 2/2/05 beacause i want to rest those days

but execel do not calculate those days why its that????

I NEED THAT ANSWER HURRY UP HELP ME... THANKS

FELIX

[felixg]

hey alan,

How you doing.

i have this problem, when u try to rest tow cell from excel, excel display this:

a1= 4/2/05 (dd/mm/yy)

a2= 2 (numeric)

a3= a1-a3 so

a3 should be: 2/2/05 beacause i want to rest those days

but excel do not calculate those days why its that????

I NEED THAT ANSWER HURRY UP HELP ME... THANKS

FELIX

[Alan]

Hi felixg,

hey alan,

How you doing.

i have this problem, when u try to rest tow cell from excel, excel display this:

a1= 4/2/05 (dd/mm/yy)

a2= 2 (numeric)

a3= a1-a3 so

a3 should be: 2/2/05 beacause i want to rest those days

but excel do not calculate those days why its that????

I NEED THAT ANSWER HURRY UP HELP ME... THANKS

FELIX

Works for me (Excel 2000).

1) Open new workbook

2) In A1 enter:

4/2/05

Press enter

{Displays as 04/02/2005 for me but that depends on settings in Windows I believe}

3) Enter:

2

(numeric) in A2

Press Enter

4) In A3 enter:

=A1-A2

Press enter



Displays as 02/02/2005 for me

If it still isn't working for you, check that the entry in A1 really is a date. Its true value should be 38387 which you can check by highlighting A1 in the formula in A3 and pressing F9 to evaluate.


HTH,

Alan.