Skip to content

Excel Formulae Ref

IF & IFS

IFS with catch-all "ELSE" ==> USE "TRUE" as the last condition

=IFS([@Category]="AAA","XXX",[@Category]="BBB","BBB",TRUE,"ZZZ")

SWITCH

SWITCH: the last one is the catch-all

=SWITCH([@Category],"AAA","BBB","CCC","DDD","ZZZ")

DateTime Conversion

FORMULA (assuming datetimeinseconds number is in cell A1):

=((A1+7,200)/86,400)+25569

Notes: - A1 = Cell with the FSE Unix Timestamp - 7,200 = 2 Hours in milliseconds (I am in UTC+2 or GMT+2) - 86,400 = Number of seconds per day - 25569 = Number of days between 1/1/1900 and 1/1/1970

Format the cell as a date/time, and you're golden!

CHOOSE Formula/Function

First Monday of the month

=DATE(YEAR(A1),MONTH(A1),CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),2,1,7,6,5,4,3))

FROM: http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=106:choose-function-in-excel&catid=78&Itemid=474

REF:

  • IFS & SWITCH
    • https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/15988036-for-the-ifs-function-give-an-option-to-give-an-el
    • https://www.techonthenet.com/excel/formulas/ifs.php
  • DateTime Conversion
    • https://productforums.google.com/forum/#!topic/docs/cXhne_Ys_QU