Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Fomula for number of days on each month from a date range

Hello All I need help

Basically I need to have the formula to reflect the number of days each
month till the end of a date

and whenever I enter in a date range each month updates itself with the
number of days

For example

Start Date 9/6/06
End Date 2/6/07


Year 2006- Year 2007-
Jan: Jan:31
Feb: Feb: 6
Mar:
April:
May:
June:
July:
August:
September:25 (including start date day)
October: 31
November: 30
December: 31

Totaling 154 Days


Im Thinking A2 is start date B2 is End Date
Then D2 E2 F2 G2 etc shows each number of days..
Can Someone Help??

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Fomula for number of days on each month from a date range

Here's one way. The output will be different!

A2 = start date = 9/6/2006
B2 = end date = 2/6/2007

D1 = header = Month/Year
E1 = header = Days

Enter this formula in D2:

=IF(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1)<B$2,TEXT(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1),"mmmm
yyyy"),"")

Enter this formula in E2:

=IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:2)-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))+1,"")

Select both D2 and E2 and copy down until you get blanks. The output will
look like this:

...................D.....................E
1.......Month/Year...........Days
2......September 2006......25
3......October 2006..........31
4......November 2006.......30
5......December 2006........31
6......January 2007............31
7......February 2007...........6

Biff

wrote in message
ups.com...
Hello All I need help

Basically I need to have the formula to reflect the number of days each
month till the end of a date

and whenever I enter in a date range each month updates itself with the
number of days

For example

Start Date 9/6/06
End Date 2/6/07


Year 2006- Year 2007-
Jan: Jan:31
Feb: Feb: 6
Mar:
April:
May:
June:
July:
August:
September:25 (including start date day)
October: 31
November: 30
December: 31

Totaling 154 Days


Im Thinking A2 is start date B2 is End Date
Then D2 E2 F2 G2 etc shows each number of days..
Can Someone Help??



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Fomula for number of days on each month from a date range

I tried pasting the formula on D2 and it states that as of Date "Month"
for


MONTH*(A$2)+ROWS($1:1)-1 IS Invalid?? is there somthing im doing
wrong?



Biff wrote:
Here's one way. The output will be different!

A2 = start date = 9/6/2006
B2 = end date = 2/6/2007

D1 = header = Month/Year
E1 = header = Days

Enter this formula in D2:

=IF(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1)<B$2,TEXT(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1),"mmmm
yyyy"),"")

Enter this formula in E2:

=IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:2)-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))+1,"")

Select both D2 and E2 and copy down until you get blanks. The output will
look like this:

..................D.....................E
1.......Month/Year...........Days
2......September 2006......25
3......October 2006..........31
4......November 2006.......30
5......December 2006........31
6......January 2007............31
7......February 2007...........6

Biff

wrote in message
ups.com...
Hello All I need help

Basically I need to have the formula to reflect the number of days each
month till the end of a date

and whenever I enter in a date range each month updates itself with the
number of days

For example

Start Date 9/6/06
End Date 2/6/07


Year 2006- Year 2007-
Jan: Jan:31
Feb: Feb: 6
Mar:
April:
May:
June:
July:
August:
September:25 (including start date day)
October: 31
November: 30
December: 31

Totaling 154 Days


Im Thinking A2 is start date B2 is End Date
Then D2 E2 F2 G2 etc shows each number of days..
Can Someone Help??


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Fomula for number of days on each month from a date range

Here's a sample file based on your posted data:

Months-Days.xls 17.0 kb

http://cjoint.com/?ljegzH0xqU

Biff

wrote in message
ups.com...
I tried pasting the formula on D2 and it states that as of Date "Month"
for


MONTH*(A$2)+ROWS($1:1)-1 IS Invalid?? is there somthing im doing
wrong?



Biff wrote:
Here's one way. The output will be different!

A2 = start date = 9/6/2006
B2 = end date = 2/6/2007

D1 = header = Month/Year
E1 = header = Days

Enter this formula in D2:

=IF(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1)<B$2,TEXT(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1),"mmmm
yyyy"),"")

Enter this formula in E2:

=IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:2)-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))+1,"")

Select both D2 and E2 and copy down until you get blanks. The output will
look like this:

..................D.....................E
1.......Month/Year...........Days
2......September 2006......25
3......October 2006..........31
4......November 2006.......30
5......December 2006........31
6......January 2007............31
7......February 2007...........6

Biff

wrote in message
ups.com...
Hello All I need help

Basically I need to have the formula to reflect the number of days each
month till the end of a date

and whenever I enter in a date range each month updates itself with the
number of days

For example

Start Date 9/6/06
End Date 2/6/07


Year 2006- Year 2007-
Jan: Jan:31
Feb: Feb: 6
Mar:
April:
May:
June:
July:
August:
September:25 (including start date day)
October: 31
November: 30
December: 31

Totaling 154 Days


Im Thinking A2 is start date B2 is End Date
Then D2 E2 F2 G2 etc shows each number of days..
Can Someone Help??



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mileage Claim Formula johndavies New Users to Excel 4 August 14th 06 09:24 AM
1st Monday of a month in date range?? scwilly Excel Worksheet Functions 22 April 26th 06 04:49 AM
Formula for determining if two date columns fall within specific date range Igottabeme Excel Worksheet Functions 2 April 21st 06 02:50 AM
Formula for determining if two date columns fall within specific date range Igottabeme Excel Discussion (Misc queries) 1 April 20th 06 10:03 PM
Match function...random search? Les Excel Worksheet Functions 10 July 28th 05 11:54 AM


All times are GMT +1. The time now is 05:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"