Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
1st Monday of a month in date range?? | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions |