Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Calculate holiday rate across holiday seasons

I have a tricky calculation I need to perform . . . I have the following data (this is a truncated version of it)

Season Start End Rate
--------------------------------------
Peak 1 01/01/2020 05/01/2020 1210
Peak 2 01/06/2020 31/08/2020 1210
Mid 01/09/2020 31/10/2020 1034
OffPeak 06/01/2020 31/05/2020 858



The data above is held in B7:D10 and are different holiday seasons and the rates charged

In B2 and C2 I have the arrival date and departure date which the formula below is referencing.

=LOOKUP(2,1/($B$7:$B$10<=$B$2)/($C$7:$C$10=$C$2),$D$7:$D$10)

At the moment it's giving me the correct number of days stay as long as the stay does not go across the 'seasons' for example from Peak to Mid.

I need a formula that will calculate how many days are in each season - for example, 30/8/2020 to 5/9/2020 would have some days in Peak and some in Mid season and, if possible, give me the appropriate rate for each.

TIA

Evets
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Calculate holiday rate across holiday seasons

Hi Steve,

Am Wed, 18 Mar 2020 11:29:24 -0700 (PDT) schrieb Evets Snomis:

I have a tricky calculation I need to perform . . . I have the following data (this is a truncated version of it)

Season Start End Rate
--------------------------------------
Peak 1 01/01/2020 05/01/2020 1210
Peak 2 01/06/2020 31/08/2020 1210
Mid 01/09/2020 31/10/2020 1034
OffPeak 06/01/2020 31/05/2020 858


did you get my mail?


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Calculate holiday rate across holiday seasons

Hi Claus

great to hear from you. I didn't get your email, I've probably changed addresses and the one registered on here isn't my live one.

Try this until we can communicate privately " evetsreg at outlook.com "

Steve
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate Holiday Johnny[_5_] Excel Discussion (Misc queries) 2 May 17th 10 03:56 PM
Holiday Farhad Excel Discussion (Misc queries) 2 October 29th 09 07:05 PM
how do I add words, holiday =1, holiday am=0.5 GCC Excel Worksheet Functions 2 March 6th 07 04:53 PM
Holiday Planner show holiday taken? Mac5 Excel Worksheet Functions 0 July 18th 06 11:29 PM
Holiday Pay johnrann New Users to Excel 5 July 17th 05 02:33 PM


All times are GMT +1. The time now is 03:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"