FIFO Validation Mismatch & Stock in Hand 0 for Specific Date Formats (August Dates DD/MM vs MM/DD)

AppSheet AppSheet Q&A
1

Hi Everyone,

I am facing an issue with my FIFO inventory validation and “Stock in Hand” calculation in AppSheet for specific August dates.

The Problem:

  1. Everything works fine for most dates (e.g., 20-04-2025).
  2. However, specifically for August dates like 01-08-2026, 04-08-2026, 05-08-2026, and 10-08-2026:
    • “Stock in Hand” shows 0 even though available balance exists in the backend sheet.
    • FIFO validation fails or gets bypassed because the scanned date is not matching correctly with the [IQC Date] column in AppSheet.

Details & Format Context:

  • I scan QR/Barcode data containing date strings (e.g., 12012749 M 04/08/2026 K7910... or 12029495 M 10-08-2026 AW2013V...).
  • Most scanned dates follow MM/DD/YYYY, but these August dates are coming as DD/MM/YYYY or DD-MM-YYYY.
  • My Google Sheet locale is set to DD/MM/YYYY.

My Current Working Expressions:

1. Scan Data - Valid_If Formula:

AND(
  LEN(LEFT([Scan Data], 8)) = 8,
  INDEX(SPLIT([Scan Data], " "), 2) = "M",
  CONTAINS([Scan Data], "Qty"),
  CONTAINS([Scan Data], "MFG"),
  CONTAINS([Scan Data], "Lot"),
  DATE(
    CONCATENATE(
      INDEX(SPLIT(INDEX(SPLIT([Scan Data], " "), 3), "/-"), 2),
      "/",
      INDEX(SPLIT(INDEX(SPLIT([Scan Data], " "), 3), "/-"), 1),
      "/",
      INDEX(SPLIT(INDEX(SPLIT([Scan Data], " "), 3), "/-"), 3)
    )
  )
  <= 
  MIN(
    SELECT(
      Stock in Hand[IQC Date],
      AND(
        [ERP Code] = NUMBER(LEFT([_THISROW].[Scan Data], 8)),
        [Balance Qty] > 0
      )
    )
  )
)

Stock in Hand
SUM(
SELECT(
Stock in Hand[Balance Qty],
AND(
[ERP Code] = NUMBER(LEFT([_THISROW].[Scan Data], 8)),
OR(
[IQC Date] = DATE(INDEX(SPLIT([_THISROW].[Scan Data], " "), 3)),
AND(
DAY([IQC Date]) = 1,
MONTH([IQC Date]) = 8,
YEAR([IQC Date]) = 2026,
CONTAINS([_THISROW].[Scan Data], “01/08/2026”)
),
AND(
DAY([IQC Date]) = 1,
MONTH([IQC Date]) = 8,
YEAR([IQC Date]) = 2026,
CONTAINS([_THISROW].[Scan Data], “01-08-2026”)
)
)
)
)
)

Oldest stock formula

MIN(
SELECT(
Stock in Hand[IQC Date],
AND(
TEXT([ERP Code]) = LEFT([_THISROW].[Scan Data], 8),
[Balance Qty] > 0
)
)
)

image
image799×561 19.2 KB

image
image806×563 19.1 KB

image
image812×586 20.6 KB

2

I’m confused even before considering your problem. This should not work:

SPLIT(INDEX(SPLIT([Scan Data], " "), 3), "/-")

This should split the result of INDEX(SPLIT([Scan Data], " "), 3) around the two-character delimiter, /-. It should not correctly split dates in either DD/MM/YYYY or DD-MM-YYYY formats. To work as written, dates would have to be formatted as DD/-MM/-YYYY.

3

In addition to Steve’s superb observation, you have mentioned following :

You mentioned most scanned dates follow MM/DD/YYYY but these August dates are showing up as DD/MM/YYYY.

For any logic to work reliably across a large data set, there needs to be an established pattern. Are only August dates received in DD/MM/YYYY format? In that case you could relatively easily add a logic that if the current running actual month is August, then treat dates as DD/MM/YYYY format and in other months MM/DD/YYYY format.

However there could still be a few false positives and false negatives with this logic. because if an August stamped material is received on 1st September, it may be disregarded or misrecognized.