Hi,
I need to determine the number of days covered on a date range for a certain month.
Say column B has start date, column C has end date, and the consecutive columns are the different months. =DAY(C2-B3)/30 worked but it didnt cover overlapping months. please see attached for sample. sample1.xlsx
Thanks for the help.
Can you update your sample workbook, instead of putting in non-working formulas, please fill out the entire table with the expected results so the end goal is 100% clear.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
My apologies,
here is with the desired result.
thanks.
If you change the dtaes in the header row so they are real dates (1st of each month) then you can use this formula in C2 copied across and down
=MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2)+1)/DAY(EOMONTH(C$1,0))
I formatted the cells so that zeroe display as blanks
Last edited by daddylonglegs; 08-27-2014 at 06:19 PM.
Audere est facere
Thanks for the help.
Just one more concern, i am getting 100%s on rows with no entries. is there any way to have these blank? thanks.
Capture.JPG
here's a sample sheet. i need to have an empty row to add future entries.
Last edited by jayp_; 08-27-2014 at 08:00 PM.
Try changing to this version in F5
=MAX(0,MIN(EOMONTH(F$4,0),N($E5))-MAX(F$4,$D5)+1)/DAY(EOMONTH(F$4,0))
copied across and down
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks