Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate days in a month
I have data in columns as follows:
Start date | End date | April | May | June What I am trying to get is how many days are in each of the column months. For instance : Start date - Jan-4-08 End date: April-2-2008 then a formula that will post 2 in column April , 0 in May and 0 in June 15-April-08 to 9th June 2008 will put 15 , 31 , 9 in respective columns 02-June-08 to 09-July-08 will put 0,0,28 thx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate days in a month
The simplest way to set this up (at least to me) would be to put the date
for the last day of the each of your header months in Row 1 (where you show April, May and June) and Custom Format them with mmmm so only the month will show. To put the last day of each month in those header cells, put this formula in where April is to be displayed... =DATE(2008,COLUMNS($A:D)+1,0) and copy it across. Note that the "D" in "COLUMNS($A:D)" is the 4th letter of the alphabet corresponding to April which is the 4th month of the year. When you copy across, the "D" will increment to "E" and "F" respectively. Remember, Custom Format these cells with mmmm to display only the month. Okay, now that we have the last day of each month available to us, the formula that goes in Row 2 (first data row) becomes somewhat simpler to write... =IF(MONTH($A2)=MONTH(C$1),C$1-$A2,IF(AND(MONTH($A2)<MONTH(C$1),MONTH($B2)MONTH( C$1)),DAY(C$1),$B2-C$1+DAY(C$1))) Copy this across, then copy them down. Note that the above formula is dependent on the starting cell for the month headers being C1 (change that reference as needed). -- Rick (MVP - Excel) "N Harkawat" wrote in message ... I have data in columns as follows: Start date | End date | April | May | June What I am trying to get is how many days are in each of the column months. For instance : Start date - Jan-4-08 End date: April-2-2008 then a formula that will post 2 in column April , 0 in May and 0 in June 15-April-08 to 9th June 2008 will put 15 , 31 , 9 in respective columns 02-June-08 to 09-July-08 will put 0,0,28 thx |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate days in a month
hi, !
I have data in columns as follows: Start date | End date | April | May | June What I am trying to get is how many days are in each of the column months. For instance: Start date - Jan-4-08 End date: April-2-2008 then a formula that will post 2 in column April , 0 in May and 0 in June 15-April-08 to 9th June 2008 will put 15 , 31 , 9 in respective columns 02-June-08 to 09-July-08 will put 0,0,28 assumptions: - row1 = titles April, May & June are "real" date-entries (the last day each month) w/ custom format: "mmm" - first account in cell [C2] w/ the formula: =sumproduct(--isnumber(match(row(indirect($a2&":"&$b2)),row(indi rect(date(year(c$1),month(c$1),0)+1&":"&c$1)),0))) - copy-cross then copy-down note: first and last matching days are included - revise your expectations for 15,31,9 (16,31,9) and 0,0,28 (0,0,29) hth, hector. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate days in each month | Excel Worksheet Functions | |||
How to calculate days in the month | Excel Discussion (Misc queries) | |||
Calculate Number of Days in a Month | Excel Worksheet Functions | |||
How to calculate a date: first day of the month after 60 days | Excel Discussion (Misc queries) | |||
Calculate Days in a Month | Excel Discussion (Misc queries) |