Counting Formula

BrettOlbrys1

New Member
Joined
May 1, 2018
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Based on a week date, I want to assign a "1" to that date. The dates greater than that date, I want the value to count up by +1 and the dates less than that date, I want them to count down by -1. My file is not static though because based on when the file is opened, the numbers assigned to the static dates will move. I need a formula that will assign the numbers above the dates.


If today was 4/19
-5-4-3-2-10123456789
3/83/153/223/294/54/124/194/265/35/105/175/245/316/76/14
If today was 5/10
-8-7-6-5-4-3-2-10123456
3/83/153/223/294/54/124/194/265/35/105/175/245/316/76/14
If today was 5/24
-10-9-8-7-6-5-4-3-2-101234
3/83/153/223/294/54/124/194/265/35/105/175/245/316/76/14
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,989
Office Version
  1. 365
  2. 2010
Is this what you mean?

Book3
ABCDEFGHIJKLMNO
1If today was19-Apr
2-5-4-3-2-10123456789
33/815-Mar22-Mar29-Mar5-Apr12-Apr19-Apr26-Apr3-May10-May17-May24-May31-May7-Jun14-Jun
4
5
6If today was10-May
7-8-7-6-5-4-3-2-10123456
83/815-Mar22-Mar29-Mar5-Apr12-Apr19-Apr26-Apr3-May10-May17-May24-May31-May7-Jun14-Jun
9
10
11If today was24-May
12-10-9-8-7-6-5-4-3-2-101234
133/815-Mar22-Mar29-Mar5-Apr12-Apr19-Apr26-Apr3-May10-May17-May24-May31-May7-Jun14-Jun
Sheet8
Cell Formulas
RangeFormula
B12:O12,B7:O7,B2:O2B2=A2+1
 
Last edited:

BrettOlbrys1

New Member
Joined
May 1, 2018
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Is this what you mean?

Book3
ABCDEFGHIJKLMNO
1If today was19-Apr
2-5-4-3-2-10123456789
33/815-Mar22-Mar29-Mar5-Apr12-Apr19-Apr26-Apr3-May10-May17-May24-May31-May7-Jun14-Jun
4
5
6If today was10-May
7-8-7-6-5-4-3-2-10123456
83/815-Mar22-Mar29-Mar5-Apr12-Apr19-Apr26-Apr3-May10-May17-May24-May31-May7-Jun14-Jun
9
10
11If today was24-May
12-10-9-8-7-6-5-4-3-2-101234
133/815-Mar22-Mar29-Mar5-Apr12-Apr19-Apr26-Apr3-May10-May17-May24-May31-May7-Jun14-Jun
Sheet8
Cell Formulas
RangeFormula
B12:O12,B7:O7,B2:O2B2=A2+1
No, because you are starting with a hard-coded values in column A. Someone previously gave me a formula for a month counter, but I can't seem to figure out how to adjust it for the weeks. The month formula they provided me was:

=IF(AJ98<EOMONTH(TODAY(),-1)+1,-DATEDIF(AJ98,EOMONTH(TODAY(),-1)+1,"m")+1,DATEDIF(EOMONTH(TODAY(),-1)+1,AJ98,"m")+1)

I need something similar for weeks.
 

johnnyL

Active Member
Joined
Nov 7, 2011
Messages
439
@BrettOlbrys1 I think a little more info would help the members help you.

Are you going to place the '1' in the desired column cell, or are you wanting that to be determined by a cell that has a date that you wish to match in your table of values? If so, will this cell that has the desired date to match be changed, or is it a set it once and forget it?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,989
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

In addition to JohnnyL's question, is the start date in B3, for example, an entry you'll make then increments by a week at a time?

So, could it look like this?

Cell Formulas
RangeFormula
B12:O12,B7:O7,B2:O2B2=A2+1
C13:O13,C8:O8,C3:O3C3=7+B3
 

johnnyL

Active Member
Joined
Nov 7, 2011
Messages
439
@kweaver That doesn't look right. The OP stated to have a '1' over the 'If today was' date. You now have a '4' over the the first date of 19-Apr, etc.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,989
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

That's what confuses me. If A2 = -5, that's one thing, but if A2 = -2, that's different because the OP also said to count + 1 from that value.
So, at least I need some clarification what it's supposed to be.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,558
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
That's what confuses me. If A2 = -5, that's one thing, but if A2 = -2, that's different because the OP also said to count + 1 from that value.
So, at least I need some clarification what it's supposed to be.

Maybe using the formula below in A2
=1-MATCH(B1,B3:O3,0)

I don't understand what A3 (3/8) means (???)

M.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,095
Office Version
  1. 365
Platform
  1. Windows
I don't understand what A3 (3/8) means (???)
8 March?


@BrettOlbrys1
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Your dates are all one week apart and all of your examples have "today's date" as one of the values in the row. What should happen if today's date is not actually in the list?

Assuming today's date is in the list, here are two possible methods. The first requires Excel 365 and only needs to be entered in the first cell.
The second - should work in all versions - needs to be copied across the row.

Todays' date for me when posting this is 11 May

21 05 11.xlsm
ABCDEFGHIJK
1-5-4-3-2-1012345
230-Mar-2106-Apr-2113-Apr-2120-Apr-2127-Apr-2104-May-2111-May-2118-May-2125-May-2101-Jun-2108-Jun-21
3
4-5-4-3-2-1012345
530-Mar-2106-Apr-2113-Apr-2120-Apr-2127-Apr-2104-May-2111-May-2118-May-2125-May-2101-Jun-2108-Jun-21
Count Dates
Cell Formulas
RangeFormula
A1:K1A1=SEQUENCE(,COUNT(A2:K2),(A2-TODAY())/7+1)
A4:K4A4=(A5-TODAY())/7+1
Dynamic array formulas.
 

johnnyL

Active Member
Joined
Nov 7, 2011
Messages
439
That's what confuses me. If A2 = -5, that's one thing, but if A2 = -2, that's different because the OP also said to count + 1 from that value.
So, at least I need some clarification what it's supposed to be.
Hopefully @BrettOlbrys1 can correct me if I am wrong here in my assumptions to the intent that the OP indicated ...

1) First row, we have a 'If today was' and then some date which is to be used for comparison to the dates that are in the 3rd row.
2) Below that, we have 2 rows, row 2 is to be used as counter values before and after a matched date found in row 3.
3) When a match is found in row3 to the date in row 1, assign row 2 in that matching column to '1'.
4) Increment/decrement the surrounding cells in row2 in respect to the offset of the '1'.

So I further assume, if the date in row1 is changed, I would assume a different column/row would have to be assigned the '1', and left/right cells to that would be incremented/decremented.

Repeat process for additional rows/columns.

15 columns wide of dates and counter values
 

Watch MrExcel Video

Forum statistics

Threads
1,132,895
Messages
5,655,830
Members
418,242
Latest member
k3itall

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top