I need to calculate the # of days in a service period within a date range and then prorate costs based upon those days. I've attached a sample worksheet and the date range I need to calculate on is 04/20/20 - 07/12/20. Thanks in advance.
I need to calculate the # of days in a service period within a date range and then prorate costs based upon those days. I've attached a sample worksheet and the date range I need to calculate on is 04/20/20 - 07/12/20. Thanks in advance.
I'm not sure if this is what you are looking for or not, but here's a formula I put in F2 and then copy down and across:
=IFERROR(DAYS(IF($C2>EOMONTH(F$1,0),EOMONTH(F$1,0),IF($C2<F$1,"",$C2)),IF($B2<F$1,F$1,IF($B2>EOMONTH(F$1,0),"",$B2)))+1,0)
In order for this to work (and be dynamic), I changed your headings to reflect the first day of the month (see attached). This can be handled many different ways, but for simplicity, I chose this.
Also, in Cell J2 I entered this:
=$E2/$D2*F2
I then copied this down and across.
Is this what you're looking for?
Hi Greg,
I copied and pasted the formula to calculate the days in F2 and it didn't work. I got the result '0'. Your tip to calculate J2, however, did work. Any further help on how to calculate F2?
I don't know how J2 could work if F2 didn't. I can't tell what the problem is if you don't attach your file. Did you open mine and did it show the right results?
in F2
Please Login or Register to view this content.
Quang PT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks