I have two columns of dates, arrival & departure, (when people stayed at our hotel). I need to intoduce column(s) to calculate and show how many days fell within the month - ie
If a guest arrived on the 28th January and left on the 3rd of February (6 nights), I need to show that the number of nights within January where 4 and that 2 where within February.
Can anyone help? PLEASE
A lot of logic steps to go through there, so I just set up a chart. This will decipher any two dates and put the days for each month into the appropriate column.
Last edited by JBeaucaire; 01-30-2009 at 07:27 AM.
_________________
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!)
I've changed the book above to include the ability to detect dates that cross several months. If you downloaded the earlier version, look at this one.
Hello JBeaucaire,
Wouldn't that give 3 in Jan and 3 in Feb for 28th Jan to 3rd Feb when the stated requirement is 4 and 2?
This formula in F2 copied across and down should give the correct result
=IF($C2="","",MAX(0,MIN(G$1,$C2)-MAX(F$1,$B2)))
You need to always be one month ahead in the header, e.g. if you have dates up to Dec 2009 then you need to include Jan 2010. If you don't want to see zeroes you can format these to show blank
Floored. As usual.
Only last thing would be to properly show the 3rd row splitting between January and December. My formulas ignored the year portion for determination of month inclusion, so december 08 dates appeared in Dec, too.
Last edited by JBeaucaire; 01-30-2009 at 07:57 AM.
Help! I have a similar issue to that listed above - I'm trying to calculate the number of nights patients stay at a free hospital Lodge. The Lodge is open M-F so there are up to 4 possible nights a patient could stay over in a given week. I also need to be able to exclude holidays from the equation. If this possible in excel????
Receiving Date Return Date Jan Feb Mar
02/17/09 03/16/09 02/17/09 03/17/09 04/17/09
A*
General Conditions:
-Month only have a max of 20 work days
-A full month end same day of following month
1st month
-If # of days <15 days then price is $100.00 / day
-If # of days >14 days then price is $1000.00 full month
2nd month and beyhond
-If # of days <15 days then price is $90.00 / day
-If # of days >14 days then price is $1000.00 full month
A* - I need to know how many working days on each month I have and apply the rules from the general conditions.
Any help?
Last edited by toze.carneiro; 02-25-2009 at 01:05 PM.
THANK YOU ! You saved me many hours
This was perfect, but I need to count the start date and end date (i.e. 03/30/13 to 4/02/13 would be 4 days). What do I change?Originally Posted by JBeaucaire
On JB's last attachment try changing F2 formula to this
=IF($C2="","",MAX(0,MIN(G$1,$C2+1)-MAX(F$1,$B2)))
and copy across and down
Audere est facere
Many thanks, but I was referring to his post from 2009 that breaks the days out by month.
=IF(H7="",0,IF(AND(MONTH($G7)=MONTH(J$6),MONTH($H7)=MONTH(J$6)),$H7-$G7,IF(MONTH($G7)=MONTH(J$6),IF(DATE(YEAR($G7),MONTH($G7)+1,1)-1-$G7>$H7-$G7,$H7-$G7,DATE(YEAR($G7),MONTH($G7)+1,1)-1-$G7),IF(MONTH($H7)=MONTH(J$6),DAY($H7),IF(AND(MONTH($G7)<MONTH(J$6),MONTH($H7)>MONTH(J$6)),DAY(DATE(YEAR($H7),MONTH(J$6)+1,1)),"")))))
My version gives the same results - just with a lot fewer characters......
Outstanding! I have it working. Again, many thanks.
Try plugging this formula into the cell you need to populate with the day count.
=MAX(IF(K2>DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(EOMONTH(TODAY(),0))),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(EOMONTH(TODAY(),0))),K2)-IF(J2<DATE(YEAR(TODAY()),MONTH(TODAY()),1),DATE(YEAR(TODAY()),MONTH(TODAY()),1),J2)+1,0)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks