In the range A2:B6 are file names and their corresponding creation date.
How could we, for example, count the number of Excel files (".xls") created in April?
Solution:
Use the SUMPRODUCT, MONTH, and RIGHT functions as shown in the following formula:
=SUMPRODUCT((MONTH(B2:B6)=C2)*(RIGHT(A2:A6,3)=C3))
Screenshot // Counting the Number of Excel Files in a List According to File Type and Date Criteria
Can Excel reference a calendar?
Julie Allen wrote on May 29, 2005 19:41 EST
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??
Reply: Julie Allen
Alan wrote on May 31, 2005 02:20 EST
Hi Julie,
[QUOTE=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??[/QUOTE]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 wrote on May 31, 2005 20:45 EST
Hi Alan,
I have this formula figured already. Thanks :)
The problem is the need to adjust [B]these date ranges [/B] 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
Reply: jallen
Alan wrote on June 01, 2005 03:03 EST
Hi Julie,
[QUOTE=jallen]Hi Alan,
I have this formula figured already. Thanks :)
The problem is the need to adjust [B]these date ranges [/B] 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[/QUOTE]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.
video poker strategy
video poker strategy wrote on July 13, 2005 10:35 EST
problem
felixg wrote on October 17, 2005 13:50 EST
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:
problem
felixg wrote on October 17, 2005 13:52 EST
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:
Reply: felixg
Alan wrote on October 17, 2005 18:33 EST
Hi felixg,
[QUOTE=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:[/QUOTE]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.
COUTNTIF USING DATE FORMAT
SPUT wrote on December 31, 1969 19:00 EST
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
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
The Intelligent Investor: The Classic Bestseller on Value Investing
