[SOLVED]Invoiced days per month

**txbullets** · 03-03-2009, 02:50 PM

I am trying to help someone with a function and can not figure out what would be best to use. What I have is a start rent date and an end rent date. They want to break down how many days in each month the item was on rent so they have a column for each month. For example say an item was rented on 12/14/08 and returned on 1/12/09. It was rented for a total of 30days 18 days in December and 12 days in January. So I would need a function in each column that would return a 14 in the December Column and a 12 in the January Column. Any help would be greatly appreciated as there are over 350 rows and they are doing the process manually right now and taking several days. Thank you in advance.

Also I just noticed that the date range is currently entered as a text in the format 2008-12-14 so YYYY-MM-DD. Don't know if the text will be a problem.

Just attached an expample. In it I am trying to find a function to fill in the green cells.

**shg** · 03-03-2009, 03:17 PM

Please Login or Register  to view this content.

The formula in C2 and copied across and down is

=MAX(0, MIN(EOMONTH(C$1, 0), $B2) - MAX(C$1, $A2) + 1)

EOMONTH requires the Analysis ToolPak.

**txbullets** · 03-03-2009, 04:04 PM

ok this function works but I have to go in and manually change the format of every one of my dates. they are in a text format and are set up like YYYY-MM-DD I put in a concatenate formula to with mid, right , left and then pasted values over it but that does not seem to work either. what am I doing wrong to get these into "date" format?

**JBeaucaire** · 03-03-2009, 04:05 PM

To change your text dates to real dates, highlight them ALL, select:

Data > Text to Columns > Delimited > Next > Next > Column Format [x] Date > Finish

That fixes that.

EDIT: Use the function above provided by SHG.

**txbullets** · 03-03-2009, 04:19 PM

Thank you everyone for you help. I forgot about the delimite abilitity to convert to date. Thanks a bunch. i love when my boss tells me there is no easy way to do something and I prove them wrong!!!!

**shg** · 03-03-2009, 04:23 PM

BTW, the dates in the first row are the first of each month.

Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes