Ups Sorry, try this=IF($B2>$C2,"Error Input",IF(TEXT(D$1,"ymm")=TEXT($B2,"ymm"),IF(TEXT(D$1,"ymm")=TEXT($C2,"ymm"),$C2-1,D$1)-$B2+1,IF(TEXT(D$1,"ymm")=TEXT($C2,"ymm"),DAY($C2)-1,IF(AND(TEXT(D$1,"ymm")>TEXT($B2,"ymm"),TEXT(D$1,"ymm")<TEXT($C2,"ymm")),DAY(D$1),0))))
Excel attached Rgds, dp Pada 24/06/2013 14:26, Ali menulis:
Dear De Premor,Thank you for giving it a try! It works well but with only one glitch! The formula will be used to count the number of nights spent and your formula counts the number of days!Eg: Date of Arrival: 30 March 2013; Date of Departure: 04 April 2013. That makes a total of 5 nights; 2 nights to go into Mar-13 column and 3 nights to go into Apr-13 Column.If you could fix this for me, I'll have the PERFECT formula for the job! Thank you again!On Sunday, June 23, 2013 7:08:37 AM UTC+4, De Premor wrote: =IF($B2>$C2,"Error Input",IF(TEXT(D$1,"ymm")=TEXT($B2,"ymm"),IF(TEXT(D$1,"ymm")=TEXT($C2,"ymm"),$C2,D$1)-$B2+1,IF(TEXT(D$1,"ymm")=TEXT($C2,"ymm"),DAY($C2),IF(AND(TEXT(D$1,"ymm")>TEXT($B2,"ymm"),TEXT(D$1,"ymm")<TEXT($C2,"ymm")),DAY(D$1),0)))) Excel attached Pada 23/06/2013 1:07, Ali menulis: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> 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 <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. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros <http://groups.google.com/group/excel-macros>. For more options, visit https://groups.google.com/groups/opt_out <https://groups.google.com/groups/opt_out>.-- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wannabe? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel <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 <http://groups.google.com/group/excel-macros>. For more options, visit https://groups.google.com/groups/opt_out <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/discussexcelFORUM RULES1) 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.
-- 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.
Ali-Hotel_Arrival Departure r1.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
