Hello,
I am trying to create a calculator that others in my field can use to calculate storage based on the number of days in our facility. A shipment can only be in our inventory for 3 days until we begin to charge storage. The math would be below:
•storage days starts at day 3 in the warehouse
•math I am looking for: (storage days)($.20 per Kg per storage day, but not to exceed $500 daily)
•total cost is not maxing $.20/storage days at $500 daily.
•I want the column to calculate if storage starts at day 3, else return "0"
I also want to apply a formula to column E that automatically removes "+00003, +00001, -", attached to the shipment ID, but preserving and without affecting the shipment ID in any way.
This set of characters is annoyingly attached to the shipment ID when scanned onto the spreadsheet via barcode scanner, and it increases audit time to remove from every cell (where the actual spreadsheet is well over 800 rows).
As always, I value your feedback. Any other ways to approach would be appreciated.[/XR][/RANGE]
I am trying to create a calculator that others in my field can use to calculate storage based on the number of days in our facility. A shipment can only be in our inventory for 3 days until we begin to charge storage. The math would be below:
•storage days starts at day 3 in the warehouse
•math I am looking for: (storage days)($.20 per Kg per storage day, but not to exceed $500 daily)
•total cost is not maxing $.20/storage days at $500 daily.
•I want the column to calculate if storage starts at day 3, else return "0"
I also want to apply a formula to column E that automatically removes "+00003, +00001, -", attached to the shipment ID, but preserving and without affecting the shipment ID in any way.
This set of characters is annoyingly attached to the shipment ID when scanned onto the spreadsheet via barcode scanner, and it increases audit time to remove from every cell (where the actual spreadsheet is well over 800 rows).
| Book2 | ||||||||
|---|---|---|---|---|---|---|---|---|
| E | F | G | H | I | J | |||
| 6 | Shipment ID | Kg | Date Arrived | Days in Warehouse | Storage Days | Total Cost | ||
| 7 | 4124124+0004 | 1990 | 3/20/2022 | 10 | 7 | 72786 | ||
| 8 | 67890232+00003 | 33334 | 3/22/2022 | 8 | 5 | 533334 | ||
| 9 | 67891- | 569 | 3/28/2022 | 2 | 0 | 0 | ||
| 10 | 2222231 | 570 | 3/14/2022 | 16 | 13 | 131482 | ||
| 11 | 22x4561+0001 | 570 | 3/15/2022 | 15 | 12 | 121368 | ||
Sheet1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H7:H8 | H7 | =IF(ISBLANK(G7),"0",ABS(DAYS(G7,TODAY()))) |
| I7:I11 | I7 | =IF(H7>3,H7-3,"0") |
| J7:J11 | J7 | =IF(H7>3,H7-3&MAX(500,(0.2*F7*I7)),"0") |
| H9:H11 | H9 | =IF(ISBLANK(G9),"",ABS(DAYS(G9,TODAY()))) |
As always, I value your feedback. Any other ways to approach would be appreciated.[/XR][/RANGE]
Last edited by a moderator:
