# Off Topic > Tips and Tutorials >  > [SOLVED] 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   :Smilie:  
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 
 :Confused:

----------


## 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 
 :Confused:

----------


## 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)
> ...



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.

----------

