Results 1 to 4 of 4

Thread: Help Please

  1. #1

    Help Please



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi I am currently helping a friend out with a reservation spreadsheet. The issue I have is certain dates fall into different price brackets.

    For example B2 Start date (01/05/15) C2 End Date (14/05/15) however the dates cross two price bands 5 days in low season and the remaining in high season is there a formula that can count from a list how many dates fall in low and how many in high? I have a list on one worksheet that details every date and which is Low or High can someone help please

    Cheers

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Hello
    Assuming dates in Col A, with a "Yes" flag in Cols B (if high) and C (if low). If we require totals for 200 dates we could place the following formulae in row 202:

    1) In B202: =COUNTIF(B1:B200, "Yes")
    2) In C202: =COUNTIF(C1:C200, "Yes")

    Does that do it?

  3. #3

    Post Thanks but think its a little more complicated

    Quote Originally Posted by Hercules1946 View Post
    Hello
    Assuming dates in Col A, with a "Yes" flag in Cols B (if high) and C (if low). If we require totals for 200 dates we could place the following formulae in row 202:

    1) In B202: =COUNTIF(B1:B200, "Yes")
    2) In C202: =COUNTIF(C1:C200, "Yes")

    Does that do it?
    I am working on a reservation spreadsheet, have sorted out the Gant sheet but need to calculate prices.

    for example I have a booking 1st May - 7th May (3 days 1st-3rd are priced at 10 and 4th-7th are priced at 15, when I enter start date 1st May and end date 7th may I need a formula that calculates that over the date range 3 days are at 10 and 4 days are at 15

    Ignore the....... needed to enter so it lined up!

    Sheet 1

    COL F........COL G........COL K.....COL L.......COL M
    Start ........End ..........LOW........MID.........HIG
    01/05/15...07/05/15......3............0.............4 (Need a formula to calculate Columns K / L / M) if at all possible??

    I have list in Sheet 2 with details as follows
    COL A.....COL B......COL C
    01/05/15 03/05/15 LOW
    04/05/15 04/06/15 MID
    05/06/15 01/09/15 HIG
    02/09/15 29/10/15 MID
    30/10/15 07/11/15 HIG
    08/11/15 29/12/15 LOW
    Last edited by im720; 2015-04-04 at 09:55 AM.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Its possible that someone else might be able to suggest a suitable formula, but I think that this is made difficult by the way your data is organised. This applies in particular to the dates in your Sheet2 table, being organised in groups rather than listed individually. Working with what you have, I would suggest a VBA solution with possibly one or more user defined functions to pick out the correct dates.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •