How can change data every 5 days?

[ngocanh87]

Hello all,

Data at M4,L4 change every 5 days: 1-5,6-10...,26-end

Day 01Feb, at E6=$M$4, E7=$L$4,
Day 06Feb, at F6=$M$4, E7=$L$4
...
Day 26Feb, at J6=$M$4, E7=$L$4

How can use vba or formula to change data of E6,F6..J6 follow M4,L4. Thank all

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	KẾT QUẢ SẢN LƯỢNG VÀ ĐƠN GIÁ 2023									14/02/2023
2												VND => USD	SUM WEIGHT
3	KẾT QUẢ KINH DOANH THÁNG 02								Unit / ĐVT：MT; USD	【確定計数】
4			Budget (Dự toán)	Transit Goods (Hàng đi đường)	5日 (01 -05)	10日 (06 - 10)	15日 (11 - 15)	20日 (16 - 20)	25日 (21 - 25)	28日 (26 - 28)		2137280,409	2029,712
5
6	Weight Shipment / Trọng lượng			-	2.030	2.030	2.030	2.030	2.030	2.030
7	Amount / Thành tiền			-	2.137.280	2.137.280	2.137.280	2.137.280	2.137.280	2.137.280
8	Price/ Đơn giá			-	1.053	1.053	1.053	1.053	1.053	1.053
Sheet1

Cell Formulas
Range	Formula
J1	J1	=TODAY()
E6:J6	E6	=$M$4
E7:J7	E7	=$L$4
E8:J8	E8	=E7/E6

 

[ngocanh87]

I just test and have problem, if today 16 feb data at M4 will change and the data of the old date also changes. How can I keep old date not change (keep as value)

 

[Legacy 143009]

It is not achievable by formula. You'll need VBA for this.

 

[ngocanh87]

I think can use this code, thank you so much.

Private Sub Workbook_Open()

Dim R As Range

Set R = Range("i:i")

If Format(Now, "DD/MM/YYYY") > Format(Range("i1"), "DD/MM/YYYY") Then
R = R.Value
End If

End Sub