HELP - Forecasting spreadsheet issue - day into number?

Hi All,

New to the forum - have used it many times searching for answers before but cant find any answers on my current issue.

To cut a long story short I have a forecasting spreadsheet which has date in column 1, day in column 2...
what I need to do is for example: If the day in column 2 is Fri, Sat or Sun x 1.7. If day is Mon, Tue, Wed, Thu x 1.2

I've searched and searched and cant find an answer but believe if I can turn the day into a number I could use an IF formula....

If the day in column 2 is Fri, Sat or Sun I want to times a cell by 1.7. If day is Mon, Tue, Wed, Thu I want to times the cell by 1.2

I dont think I can tell excel to work out based on name of a day, however if I can change Sun to 1, Mon to 2 etc I can then put that into an IF formula ie something like this:
IF(a1=<1&3>,x1.2,x1.7)

1-Jun 2-Jun 3-Jun
Sun Mon Tue
Rooms Sold 52 135 161
Sleeper Density 170% 120% 120%
Sleepers 88 162 193

This is a cut out of what I have at the moment but I have to populate the Sleeper density manually.... when I know there is a way of formulating it...
not a major job but when budget is around the corner I'll have to do this for 365 days!!

Afternoon Unitedrule (but they don't lol)

=if(or(text(b1,"DDD")="sun",text(b1,"DDD")="sat",text(b1,"DDD")="fri")),c1*1.7,c1*1.2)

b1 is the date, c1 is the cell you wish to multiply by 1.2 or 1.7

baj

Originally Posted by bajdr47

Afternoon Unitedrule (but they don't lol)

=if(or(text(b1,"DDD")="sun",text(b1,"DDD")="sat",text(b1,"DDD")="fri")),c1*1.7,c1*1.2)

b1 is the date, c1 is the cell you wish to multiply by 1.2 or 1.7

baj

They used to Baj....

Awesome!! Thanks for your help, works spot on!

Hi unitedrule,

Assuming the forecast dates are in column B and the value you want multiplied by 1.2 or 1.7 is in column A then the below formula should work.
=IF(WEEKDAY(B1,3)<=3,1.2,1.7)*A1

Weekday returns a number from 0 to 6 dependant on which day of the week it is. With the current return type the week starts with Monday being 0, if the value returned is less or equal to 3 (Monday to Thursday) then 1.2 is multiplied by the value in column A, else it's multiplied by 1.7.

Hope this helps!
Dai

Awesome... that worked as well!!!

Ok... how about this one...

If it says Sat I need the answer to be 100, If Sunday I need it to be 50 and any other day I need it to be 500.....

I keep getting a 50050 or 10050 answer for my formula!!