Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I break a date range by month?
I am trying to calculated the days in a month for a 14 day pay period. What
I want to do is to calculate how many days are in each month if the pay period crosses a month. For example the begining date is 8/21/05 and it ends 9/3/05. I was wondering if there is a formula that will allow me to calculated that there are 3 days in Sept and 11 in August. Thanks |
#2
|
|||
|
|||
=DATE(YEAR(start),MONTH(start)+1,0)-start+1
and =end-DATE(YEAR(end),MONTH(end),0) -- HTH RP (remove nothere from the email address if mailing direct) "HeatherDawn" wrote in message ... I am trying to calculated the days in a month for a 14 day pay period. What I want to do is to calculate how many days are in each month if the pay period crosses a month. For example the begining date is 8/21/05 and it ends 9/3/05. I was wondering if there is a formula that will allow me to calculated that there are 3 days in Sept and 11 in August. Thanks |
#3
|
|||
|
|||
I mocked up some sample data: pay period start date in column A, end
date in column B. When A2 and B2 are populated, the formula in C2 is =IF(MONTH(A3)<MONTH(B3),"Days in " & TEXT(A3,"mmmm") &": " & EOMONTH(A3,0)-A3+1 & "; Days in " & TEXT(B3,"mmmm") &": "&B3-EOMONTH(B3,-1),"Days in " & TEXT(A3,"mmmm") &": " & B3-A3+1) This will determine the number of days in a single month or in both months when the pay period spans 2 months. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function...random search? | Excel Worksheet Functions | |||
If formula for date range | Excel Discussion (Misc queries) | |||
adding occurrences for date range | Excel Discussion (Misc queries) | |||
Completion Percentage of a date range | Excel Discussion (Misc queries) | |||
Return the end of month date from a date | Excel Worksheet Functions |