Djeeni Documentation

User Tools

Site Tools

Trace:

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
en:user_guide:excelfunction [2022/04/09 09:05]
tiger [Cell/Range set cells and values] 		en:user_guide:excelfunction [2022/10/25 14:10] (current)
tiger [Dates]
Line 1: Line 1:
 {{indexmenu_n>9}} {{indexmenu_n>9}}
-====== Excel functions in Djeeni process step parameters ======+====== Excel functions in Djeeni processes ======
  
 Djeeni is created for people who have built up experience and proficiency in MS Excel by using it regularly for different data processing tasks. We do want you to be able to keep and utilize your proficiency and therefore built Djeeni to seamlessly work together with MS Excel and its powerful built-in functions. In the sections below we explain through practical examples how and for what purpose you can embed Excel functions at different Djeeni process steps. Djeeni is created for people who have built up experience and proficiency in MS Excel by using it regularly for different data processing tasks. We do want you to be able to keep and utilize your proficiency and therefore built Djeeni to seamlessly work together with MS Excel and its powerful built-in functions. In the sections below we explain through practical examples how and for what purpose you can embed Excel functions at different Djeeni process steps.
Line 57: Line 57:
   Row List Next   Row List Next
 </code> </code>
 +
 +If you are familiar with Pivot tables in MS Excel then this feature of Djeeni is an extension of the Pivot for more complex cases.
 ===== Dates ===== ===== Dates =====
 +
 +Working with dates is one of the most difficult tasks in MS Excel on three levels:
 +  * There are three main date formats: Month/Day/Year; Year-Month-Day; Day-Year-Month (here we talk about writing years with 4 digits - 2-digit year notation makes the case even more difficult). Only the Year-Month-Day format is straightforward for MS Excel.
 +  * MS Excel behaves differently according to the regional settings of the operating system. You open the same workbook (or CSV file) on two machines and one of them understands the dates while the other not, or not correctly. You cannot rely on having the date
 +  * When dates are put in formulas they will be re-processed in MS Excel; sometimes resulting in misunderstanding the date again.
 +
 +MS Excel puts a lot of undocumented effort to find and silently convert different values to dates. It makes a lot of errors: cannot find valid dates (because of the regional settings); recognizes dates where there are none; or simply misinterprets dates (e.g. makes 5th March from 3rd May) causing troubles.
 +
 +Based on these issues we suggest that you use dates in Djeeni processes following these guidelines:
 +  - Whenever possible store every date in three separate cells: year; month; day
 +  - Combine the three values (let's suppose year is A1; month is B1; day is C1 on worksheet wsSheet) into a date by [+DATE([=wsSheet!A1],[=wsSheet!B1],[=wsSheet!C1])] (using the standard year-month-date order). Djeeni will use the underlying number value of the date ensuring consistency on all platforms and locales.
 +  - Pre-format your target cells to short date or long date to display correct date strings (instead of number values).
 ===== Cell references in Excel functions ===== ===== Cell references in Excel functions =====
 +
 +There is a hidden concept in MS Excel about what it means to reference a cell. When a cell (e.g. B3) is referenced in a formula or inside a function, MS Excel reads //the value// of the cell and uses the value further. Reading the value means also to decide if it is a number or a text or a date value (the //type// of the value). Certain function parameters can be only text or number or date values and MS Excel gives an error if the given value does not have the proper type. Therefore it is important to understand how Djeeni formulas treat cells and cell values when they are combined with Excel functions.
 +
 +In Djeeni, cell references are always in the form **wsDjeeniName!ColumnRow** that can be resolved for MS Excel in two forms:
 +  * The Djeeni cell reference is transformed into an Excel cell reference. Example: if we have a sheet1 worksheet in the workbook c:\finance\export.xlsx with Djeeni name wsExport then wsExport!A2 will be passed to Excel as '[c:\finance\export.xlsx]sheet1'!A2. To achieve this the Djeeni formula [$ ... ] must be used.
 +  * The Djeeni cell reference is processed by Djeeni by reading the cell value. Example: if the above A2 cell contains 'Toy Ship', then MS Excel will get the value 'Toy Ship' to work with. To achieve this the Djeeni formula [= ... ] must be used. Djeeni does not check if the value a number or text or a date is; it is passed as a text without enclosed in "" in any case (and it is very handy e.g. when generating the dynamic part of a variable workbook name)
 +
 +On the other hand, MS Excel expects "" around text values in functions. Example: If the value of the above A2 cell must be compared to 'Toy Boat' one would write in Djeeni: IF([=wsExport!A2] = "Toy Boat",1,2); but this results in error. After processing the Djeeni formula MS Excel would see: IF(Toy Ship = "Toy Boat",1,2). 
 +
 +There are two solutions:
 +  * write: IF([$wsExport!A2] = "Toy Boat",1,2) that will be passed to MS Excel as IF('[c:\finance\export.xlsx]sheet1'!A2 = "Toy Boat",1,2)
 +  * write: IF("[=wsExport!A2]" = "Toy Boat",1,2) that will be passed to MS Excel as IF("Toy Ship" = "Toy Boat",1,2) 
 +
 +Check always which solution fits in the formula.
 +
 +
 +
 +
  
  

Page Tools

en/user_guide/excelfunction.1649487937.txt.gz ยท Last modified: 2022/04/09 09:05