classified
New Member
- Joined
- Sep 5, 2019
- Messages
- 2
Hi everyone,
I have a data table called "Order" in which a column of its raw data are strings of date; ie. 20191015 (YYYYMMDD).
So I added 3 columns to the table to extracts its Y/M/D using LEFT/RIGHT formulas ie. LEFT(RIGHT([@Date String],4),2) is Column [M].
Then I tried to sum the amount of finished goods weight at the end of each month with some criteria of the products using the following formula:
=SUMIFS(Order[FG Weight],....,Order[Y],YEAR($B5),Order[M],MONTH($B5),Order[D],"<=31")
where B5 is Excel-format date value
The result turns out to be 0 for all months and when I tried removing the date part (Order[D],"<=31"), it worked fine.
So I suspected the problem rooted from the fact that my date source is formula-based.
Does anyone know a solution to this?
I have a data table called "Order" in which a column of its raw data are strings of date; ie. 20191015 (YYYYMMDD).
So I added 3 columns to the table to extracts its Y/M/D using LEFT/RIGHT formulas ie. LEFT(RIGHT([@Date String],4),2) is Column [M].
Then I tried to sum the amount of finished goods weight at the end of each month with some criteria of the products using the following formula:
=SUMIFS(Order[FG Weight],....,Order[Y],YEAR($B5),Order[M],MONTH($B5),Order[D],"<=31")
where B5 is Excel-format date value
The result turns out to be 0 for all months and when I tried removing the date part (Order[D],"<=31"), it worked fine.
So I suspected the problem rooted from the fact that my date source is formula-based.
Does anyone know a solution to this?