Hi Vabz, Thank you very much for looking into my problem! Your solution works fine when the Date of Arrival and Date of Departure spans over 2 consecutive months but I get an error in cases where the Date of Arrival and the Date of Departure are in the same month (eg: Arrival on 24-01-13 and Departure on 30-01-13).
Is there a way to twist the formula to account for this issue? On Thursday, June 20, 2013 12:02:23 PM UTC+4, Vabz wrote: > > Hi > > PFA, there are other solution for this. > > HTH > > > On Thu, Jun 20, 2013 at 11:53 AM, Ali <hoo...@gmail.com <javascript:>>wrote: > >> Hi all, >> >> I have a worksheet that need to allocate the number of nights spent by a >> guest in the relevant Columns designed for the purpose. I can't seem to >> find the right formula to do this. >> >> Here is a brief of the situation and the problem that need to be solved: >> Column B has the Date of Arrival (say, 29 July 2012) and Column C has the >> Date of Departure (say, 03 August 2012). That makes a total of 5 nights >> spent (3 nights in July 2012 and 2 nights in August 2012). >> >> Columns D onwards have headers like Jul-12, Aug-12, Sept-12,.....June-13. >> (ie; July 2012 till June 2013). I need to formula for cell D2 whereby when >> I drag the formula across that row, it will automatically fill cells >> related to Column Jul-12 by 3 and the one related to Aug-12 by 2. >> >> Thank you for helping! >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> 2) Don't post a question in the thread of another member. >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> 4) Acknowledge the responses you receive, good or bad. >> 5) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros...@googlegroups.com <javascript:>. >> To post to this group, send email to excel-...@googlegroups.com<javascript:> >> . >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
