Find week days within the date range and return number

**CaineSmith** · 12-05-2014, 07:36 PM

Hi! Is there anyway I can get this result?

A1 = Check-in Date
B1 = Check-out Date (isn't counted)
Room rate on Sun, Mon, Tue, Wed = 100
Room rate on Thu, Fri, Sat = 110
C1 = Sum of the Room rates

For example
5/12/2014 Fri 110
6/12/2014 Sat 110
7/12/2014 Sun 100
8/12/2014 Mon 100
9/12/2014
Sum 420

Display in the worksheet
A1 B1 C1
5/12/2014 9/12/2014 420

Sorry, I don't know how to explain better than this.

**Haseeb Avarakkan** · 12-05-2014, 07:49 PM

Hello Smith,

Try this in C1

=SUMPRODUCT(LOOKUP(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1-1))),{1,5},{100,110}))

**hoyasaxa215** · 12-05-2014, 07:51 PM

Should be simple enough, but the format of the data is going to be the main issue. Can you upload a sanitized sample file showing how the data is arranged normally and what you'd like it to look like when you're done?

**Tony Valko** · 12-05-2014, 08:30 PM

As long as the Check-in and Check-out are different days...

Based on Haseeb's idea.

Array entered**:

=SUM(IF(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1-1)))>4,110,100))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**CaineSmith** · 12-05-2014, 08:30 PM

Thank you Haseeb A! I have never used SUMPRODUCT and LOOKUP before. It works great, thank you so much!!

**CaineSmith** · 12-06-2014, 01:52 PM

Thank you Tony! Now I can play with your and Haseeb's formulas in many of my reports. Very appreciate your helps.

Problem solved!!

**Tony Valko** · 12-06-2014, 03:47 PM

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Find week days within the date range and return number

LinkBack

Thread Tools

Rate This Thread

Display

Find week days within the date range and return number

Re: Find week days within the date range and return number

Re: Find week days within the date range and return number

Re: Find week days within the date range and return number

Re: Find week days within the date range and return number

Re: Find week days within the date range and return number

Re: Find week days within the date range and return number

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find yesterdays date in range and return row number to be used in offset

[SOLVED] Find first date of the month in range and return column number

[SOLVED] Find number of days greater than zero between date range.

[SOLVED] Number of Days Between Dates but if no Date go to next and return # of Days

Separating days by Year/Week in a date range :)

Bookmarks

Bookmarks

Posting Permissions