Thread: Working between two months-Dates

Hi guys,

I'm sort of an amateur user of Excel just discovering more complicated functions, but I've hit a wall on a piece of work I'm doing.

Basically I'm working out a spreadsheet for an apartment company, and I need to be able to calculate number of days between two dates, divide the rent by that and work out how much was paid for the night.
I've managed to succeed with that, but I have a slight problem concerning dates between months, e.g. 28/08/2011 until 03/09/2011- I need to be able to work out what money was paid for August, i.e. the 28/08 to the 01/09 and then the monies paid in September for the rest of the month.
This also needs some kind of message to indicate the division between the two months.
I did try doing this the long way- working out the dates between the two months and then dividing the rent accordingly but it seems a bit convoluted, has anyone got any ideas?

Do I need to explain this better? Any help on the matter would be greatly appreciated.

Kindest regards

Skip08 (newbie)

Originally Posted by Bob Phillips

The formula for the days in the first month is

=IF(MONTH(G5)>MONTH(F5),G5-F5-DAY(G5),G5-F5)

the second is

=IF(MONTH(G5)>MONTH(F5),G5-(G5-DAY(G5)),0)

By the way you just need = G5-F5 for the 'Nombre de jours'

You might also need to replace my commas (,) with semi-colons ( if you have a French language Excel.

Thanks so much for your prompt response! This has been a big help, but I need to be a bit cheeky and ask for a little bit more. The month function seems to be the way to go, but I need to include the night of the 31st in a client's stay, i.e. a stay from the 28th of August to the 5th of September would have 4 nights rather than 3 days. Is there any precision I can make to this formula to achieve this?

Edit: Just added one to one formula and subtracted from the other, SORTED!
You're a legend Bob!
Thanks again :D