Home |
Search |
Today's Posts |
#1
|
|||
|
|||
conditional formula to recognize day of the week from date
Hi, please someone help me!
I have a colums with dates, all are consecutive days. I formatted it to display the days of the week and date ( Tuesday, April 19, 2005) I have another column where I want to calculate weekly average from the first day of the week, sunday, to the last saturday. So in this column I want to write a conditional formula that if the date column in the same row is saturday then calculate the average for seven days above including saturday if not the display a blank. I'm trying to use =if( ) function but I don't know hor to tell the program to recognize if the date is saturday. Please help me. Thank you Thalia |
#2
|
|||
|
|||
You could try something along this line.
=IF(WEEKDAY(A1)=7,"Your average formula","") In the example, A1 is where your date is. HTH, Paul "tiw" wrote in message ... Hi, please someone help me! I have a colums with dates, all are consecutive days. I formatted it to display the days of the week and date ( Tuesday, April 19, 2005) I have another column where I want to calculate weekly average from the first day of the week, sunday, to the last saturday. So in this column I want to write a conditional formula that if the date column in the same row is saturday then calculate the average for seven days above including saturday if not the display a blank. I'm trying to use =if( ) function but I don't know hor to tell the program to recognize if the date is saturday. Please help me. Thank you Thalia |
#3
|
|||
|
|||
=IF(WEEKDAY(A20,1)=7,SUM(B20:OFFSET(B20,-6,0)),"") but what happens if the Saturday date doesn't have 6 days above? -- HTH RP (remove nothere from the email address if mailing direct) "tiw" wrote in message ... Hi, please someone help me! I have a colums with dates, all are consecutive days. I formatted it to display the days of the week and date ( Tuesday, April 19, 2005) I have another column where I want to calculate weekly average from the first day of the week, sunday, to the last saturday. So in this column I want to write a conditional formula that if the date column in the same row is saturday then calculate the average for seven days above including saturday if not the display a blank. I'm trying to use =if( ) function but I don't know hor to tell the program to recognize if the date is saturday. Please help me. Thank you Thalia |
#4
|
|||
|
|||
oh and replace SUM with AVERAGE
-- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... =IF(WEEKDAY(A20,1)=7,SUM(B20:OFFSET(B20,-6,0)),"") but what happens if the Saturday date doesn't have 6 days above? -- HTH RP (remove nothere from the email address if mailing direct) "tiw" wrote in message ... Hi, please someone help me! I have a colums with dates, all are consecutive days. I formatted it to display the days of the week and date ( Tuesday, April 19, 2005) I have another column where I want to calculate weekly average from the first day of the week, sunday, to the last saturday. So in this column I want to write a conditional formula that if the date column in the same row is saturday then calculate the average for seven days above including saturday if not the display a blank. I'm trying to use =if( ) function but I don't know hor to tell the program to recognize if the date is saturday. Please help me. Thank you Thalia |
#5
|
|||
|
|||
Thank you, it worked nicely.
Thalia "Bob Phillips" wrote: oh and replace SUM with AVERAGE -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... =IF(WEEKDAY(A20,1)=7,SUM(B20:OFFSET(B20,-6,0)),"") but what happens if the Saturday date doesn't have 6 days above? -- HTH RP (remove nothere from the email address if mailing direct) "tiw" wrote in message ... Hi, please someone help me! I have a colums with dates, all are consecutive days. I formatted it to display the days of the week and date ( Tuesday, April 19, 2005) I have another column where I want to calculate weekly average from the first day of the week, sunday, to the last saturday. So in this column I want to write a conditional formula that if the date column in the same row is saturday then calculate the average for seven days above including saturday if not the display a blank. I'm trying to use =if( ) function but I don't know hor to tell the program to recognize if the date is saturday. Please help me. Thank you Thalia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional validation formula | Excel Worksheet Functions | |||
conditional formula | Excel Worksheet Functions | |||
4 Day Work Week in a formula | Excel Discussion (Misc queries) | |||
How can I create formula that turns a date into the week # in | Excel Discussion (Misc queries) | |||
Syntax For Conditional Formula | Excel Worksheet Functions |