Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
en:user_guide:djeeniformula [2022/10/25 13:40] tiger [Excel functions] |
en:user_guide:djeeniformula [2022/10/25 14:22] (current) tiger [Dates in formulas] |
||
|---|---|---|---|
| Line 103: | Line 103: | ||
| **Note**: The difference between [$...] and [=...] is very important: | **Note**: The difference between [$...] and [=...] is very important: | ||
| - | * [$....] is translated by Djeeni to MS Excel syntax and left to MS Excel to read the actual value: [$wsReport!A4] will be (let's say that wsReport is located at **c: | + | * [$....] is translated by Djeeni to MS Excel syntax and left to MS Excel to read the actual value: [$wsReport!A4] will be (let's say that wsReport is located at **c: |
| * [=...] is executed by Djeeni reading the actual value of the cell: if [$wsReport!A4] contains the value **24** then the result of this formula is **24** that can be used for calculations or as text value. For date values [=...] reads the underlying number value. | * [=...] is executed by Djeeni reading the actual value of the cell: if [$wsReport!A4] contains the value **24** then the result of this formula is **24** that can be used for calculations or as text value. For date values [=...] reads the underlying number value. | ||
| Line 132: | Line 132: | ||
| Working with dates in MS Excel is //the most// complex task causing the most headaches. For sure, most of us encountered this situation: a workbook is received with merged worksheets containing a date column. Surprisingly some dates are shown as numbers, while other dates are not recognized by MS Excel and cannot be used for further processing. Also, some dates are simply changing: it was 10 March 2022 at the sender, and it is just displayed as 3 October 2022 at the receiver (March = 3; October = 10). | Working with dates in MS Excel is //the most// complex task causing the most headaches. For sure, most of us encountered this situation: a workbook is received with merged worksheets containing a date column. Surprisingly some dates are shown as numbers, while other dates are not recognized by MS Excel and cannot be used for further processing. Also, some dates are simply changing: it was 10 March 2022 at the sender, and it is just displayed as 3 October 2022 at the receiver (March = 3; October = 10). | ||
| - | Explaining the reasons and the behavior of MS Excel in these cases is beyond the scope of this manual. | + | The (almost) common denominator is the fact that all dates are internally stored in MS Excel as a number. Djeeni uses this number value whenever it is available for reading cell values and calculations. The consequence of it is that the target cells must be pre-formatted to short or long dates otherwise the number values will be seen instead of the date strings. |
| + | |||
| + | Dates are also discussed | ||
| ==== Calculations using row number and column letter of a cell ==== | ==== Calculations using row number and column letter of a cell ==== | ||