add date once a certain product reaches a certain volume of sales

[tanuxaxaxa]

Hi guys,

I have faced a problem I hope you can help me with

I need to track on which date does a certain product reached a certain volume of sales: 500 units, 1000 units, 2000 units

Product	Volume	reached 500	2000	etc
A	100	date	date
B	500	date	date
C	1500	date	date
D	3000	date	date

<tbody>
</tbody>

Honestly, I have no clue how to tackle this


Help

 

[Logit]

Excel 2007 32 bit

	A​	B​	C​	D​	E​
1​
2​	Product	Volume	reached 500	2000​	etc
3​	A	100​
4​	B	500​	10/24/18​
5​	C	498​
6​	D	3000​	10/24/18​	10/24/18​
7​
8​	In C3 copied down : =IF(B3>=500,TODAY(),"")
9​	In D3 copied down : =IF(B3>=2000,TODAY(),"")

Sheet: Sheet1

 

[tanuxaxaxa]

Excel 2007 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Product	Volume	reached 500	2000​	etc
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	A	100​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	B	500​	10/24/18​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	C	498​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	D	3000​	10/24/18​	10/24/18​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	In C3 copied down : =IF(B3>=500,TODAY(),"")
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	In D3 copied down : =IF(B3>=2000,TODAY(),"")

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

thank you for the response, but it is not working (not sure why though)

It is adding timestamp even to the cells with no value at all.

 

[Logit]

.
If you were able to view the formulas in each cell after pasting the formula, it would look like this :

	A​	B​	C​	D​
1​
2​	Product	Volume	500​	2000​
3​	A	100​	=IF(B3>=500,TODAY(),"")	=IF(B3>=2000,TODAY(),"")
4​	B	500​	=IF(B4>=500,TODAY(),"")	=IF(B4>=2000,TODAY(),"")
5​	C	1500​	=IF(B5>=500,TODAY(),"")	=IF(B5>=2000,TODAY(),"")
6​	D	3000​	=IF(B6>=500,TODAY(),"")	=IF(B6>=2000,TODAY(),"")

I surmise you have done something different than what is seen above, as I deleted the numbers
in Col B and nothing appeared in the C & D columns. So the formulas work as desired.

 

[NoSparks]

Are the volume numbers entered manually or do those cells contain formulas ?

 

[tanuxaxaxa]

Are the volume numbers entered manually or do those cells contain formulas ?

Yes, those have formulas in.
May it be the reason the above suggestion is not working?

 

[tanuxaxaxa]

.
If you were able to view the formulas in each cell after pasting the formula, it would look like this :

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Product	Volume	500​	2000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	A	100​	=IF(B3>=500,TODAY(),"")	=IF(B3>=2000,TODAY(),"")
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	B	500​	=IF(B4>=500,TODAY(),"")	=IF(B4>=2000,TODAY(),"")
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	C	1500​	=IF(B5>=500,TODAY(),"")	=IF(B5>=2000,TODAY(),"")
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	D	3000​	=IF(B6>=500,TODAY(),"")	=IF(B6>=2000,TODAY(),"")

<tbody>
</tbody>

I surmise you have done something different than what is seen above, as I deleted the numbers
in Col B and nothing appeared in the C & D columns. So the formulas work as desired.

Yes, I have done just that - but I keep getting date added to the cells with even no values in
The formula is correct, but might the issue be that I have formulas to calculate the sales volume?

 

[Logit]

.
Hmm .... curious ...

I experimented by entering :



100 in I3
500 in I4
498 in I5
3000 in I6


Then in :


B3 I entered =I3


B4 I entered =I4


B5 I entered =I5


B6 I entered =I6




The formulas worked as expected.


????

 

[NoSparks]

@ Logit
I don't think your dates will be static which is likely to be important.


@ tanuxaxaxa
If you're open to a VBA macro solution, Hui's suggestion to a question here can be adapted.

In the attached,
P1:S1 are used so volumes have formulas
Volumes are copied to column Z
Z1 has a Sumproduct formula
The worksheet calculation event is used to put in the dates

https://app.box.com/s/h2ez8e6ewsdpiyf69dwqcstgdnhcnltn

 

[Logit]

.
NoSparks :

You are correct. A macro is the best option.