Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Range Formular
Hello all at ExcelBanter,
I have been looking around the Forums for a couple of days now and as yet have been unable to find a solution to a calculation i would like to make. I have attached a copy of what i have so far. It's not much but i am new to Excel. I have a set of Seasonal Dates in Column A and B with Nightly prices etc.. In I2 and J2 are Dates that can Input by the user. What i would like to have is that column F shows up how many nights fall between the season Dates based on the user Input Dates. So in my example F6 should read as 16 and F7 as 2. Hopefully that makes sense. If anyone could give me a couple of hints or direct me to a Tutorial that might help me i would be greatful Many Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Range Formular
Hi
Using your workbook as per your values: F5 = 70 Nights F6 = 16 Nights F7 = 47 Nights. I don't know where you got 2 from, either way, the formula that you looking for is this, it will leave the cell blank if there is no date in Column A. In Cell F5 type =IF($A5="","",$B5-$A5) Copy down as required HTH Mick. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Range Formular
On 19/07/2012 1:06 AM, Diverslife wrote:
Hello all at ExcelBanter, I have been looking around the Forums for a couple of days now and as yet have been unable to find a solution to a calculation i would like to make. I have attached a copy of what i have so far. It's not much but i am new to Excel. I have a set of Seasonal Dates in Column A and B with Nightly prices etc.. In I2 and J2 are Dates that can Input by the user. What i would like to have is that column F shows up how many nights fall between the season Dates based on the user Input Dates. Just thought I would have a second read through to make sure I got what it is you were looking for and I noticed you already have the subtraction formula as you stated in your 3rd paragraph. With this in mind, if what I have posted is not what you want, then please post back with more specific details, from where I'm at, you already have the answer to your question in K2. Cheers Mick. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Range Formular
Hi,
Am Wed, 18 Jul 2012 15:06:33 +0000 schrieb Diverslife: In I2 and J2 are Dates that can Input by the user. What i would like to have is that column F shows up how many nights fall between the season Dates based on the user Input Dates. in F5 try: =IF(B5<$I$2,0,IF(AND(A5<$I$2,B5<$J$2),B5-$I$2,IF(AND(A5$I$2,B5$J$2),$J$2-A5,0))) and drag down Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Range Formular
Hi,
Am Thu, 19 Jul 2012 12:50:58 +0200 schrieb Claus Busch: in F5 try: =IF(B5<$I$2,0,IF(AND(A5<$I$2,B5<$J$2),B5-$I$2,IF(AND(A5$I$2,B5$J$2),$J$2-A5,0))) and drag down I forgot the special condition that all days fall into the season. Try in F5: =MAX((A5<$I$2)*(B5<$J$2)*(B5-$I$2),(A5=$I$2)*(B5<=$J$2)*(B5-A5),(A5$I$2)*(B5$J$2)*($J$2-A5)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Range Formular
On Wed, 18 Jul 2012 15:06:33 +0000, Diverslife wrote:
Hello all at ExcelBanter, I have been looking around the Forums for a couple of days now and as yet have been unable to find a solution to a calculation i would like to make. I have attached a copy of what i have so far. It's not much but i am new to Excel. I have a set of Seasonal Dates in Column A and B with Nightly prices etc.. In I2 and J2 are Dates that can Input by the user. What i would like to have is that column F shows up how many nights fall between the season Dates based on the user Input Dates. So in my example F6 should read as 16 and F7 as 2. Hopefully that makes sense. If anyone could give me a couple of hints or direct me to a Tutorial that might help me i would be greatful Many Thanks +-------------------------------------------------------------------+ |Filename: Price calculation.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=473| +-------------------------------------------------------------------+ It appears these are check in and check out days, and that you are counting the First Day, but are NOT counting the LAST day. Given that, the following appears to work: F5: =MAX(0,SUMPRODUCT((ROW(INDIRECT(A5&":"&B5))=$I$2) * (ROW(INDIRECT(A5&":"&B5))<=$J$2))-($J$2<=B5)) and fill down as far as required. If you do want to count the last day (in which case F7=3), then simplify the above to: =SUMPRODUCT((ROW(INDIRECT(A5&":"&B5))=$I$2)* (ROW(INDIRECT(A5&":"&B5))<=$J$2)) This formula will fail in Excel 2007 or later after 25 November 4770. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy date based on date -refer to date range | Excel Programming | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Date conversion formular | Excel Worksheet Functions | |||
any formular to deduct an amount automatically on a given date? | Excel Discussion (Misc queries) | |||
formular to change data on a certain date every month | Excel Programming |