I have 3 columns, one column having all dates of the month, like 1/10/14, 2/10/14 etc. 2nd column having values like 180, 181 etc. On the third column I need a total of column 2 +1 like that. But if 5/10/14 comes that should be blank and should start adding sum from Monday onwards.
For example:
01-Oct 181
02-Oct 182
03-Oct 183
04-Oct 184
05-Oct
06-Oct 185
07-Oct 186
08-Oct 187
09-Oct 188
10-Oct 189
11-Oct 190
12-Oct
13-Oct 191
14-Oct 192
15-Oct 193
You can use the Weekday function to find the weekday serial number of a date
1=Sunday
2=Monday
3=Tuesday
....
So, if it is Sunday you want to skip, use this
=TEXT(IF(WEEKDAY(A3)=1,"",A3+B3),"dd-mmm")
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
Hi
Please see attached sheet
Hope this is what you meant.
I have hidden a column "A" which basically looks at the date and returns the Day eg "Sunday"
Then using that created an if statement to only calculate the sum when not = to Sunday
If your dates are in col. A and starts from A1, input 181 in B1 and then try this...
In B2
and copy down.
Please Login or Register to view this content.
Is this what you are trying to achieve?
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Or alternatively use this formula in column c and drag down
=IF(TEXT(A1,"DDD")="Sun","",B1+A1)
Column A having the dates of the whole month. In Column B I need auto increment of a number say 1 from any number skipping when Sunday's occur.
For example:
01-Oct 181
02-Oct 182
03-Oct 183
04-Oct 184
05-Oct
06-Oct 185
07-Oct 186
08-Oct 187
09-Oct 188
10-Oct 189
11-Oct 190
12-Oct
13-Oct 191
14-Oct 192
15-Oct 193
If you use the formula suggested in post#4, you will get the following output.
A B 1 01-Oct 181 2 02-Oct 182 3 03-Oct 183 4 04-Oct 184 5 05-Oct 6 06-Oct 185 7 07-Oct 186 8 08-Oct 187 9 09-Oct 188 10 10-Oct 189 11 11-Oct 190 12 12-Oct 13 13-Oct 191 14 14-Oct 192 15 15-Oct 193 16 17 B1 =181 18 B2 =IF(WEEKDAY(A2)<>1,INDEX(B$1:B1,MATCH(1E+307,B$1:B1))+1,"")
ok I see use this formula in column b
=IF(B2="",B1+1,IF(TEXT(A3,"DDD")<>"Sun",B2+1,""))
if you make sure your dates start A3 and have row 1 blank and in b2 your starting number for eg 180
Thanks, works great
Please try to be precise in your replies. Your reply didn't tell us that which solution worked for you.
Anyways, if that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.
Sorry,Please try to be precise in your replies. Your reply didn't tell us that which solution worked for you.
Anyways, if that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.
Your answer works perfectly. I could have mentioned it clearly. Sorry missed it.
Thanks very much for your valuable time. Also thanks for the valuable time for every one paid attention to my question
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks