Discussions

Expand all | Collapse all

case formula returning one value

  • 1.  case formula returning one value

    Posted 12-27-2018 20:35
    Newbie here! Probably a silly error on my part but...

    I am trying to create a formula in the field "Mileage Cost" that will look at a date in the field "Date", find what year "Date" contains, and then multiply by a different number based on that year, then return that product back into the field "Mileage Cost". Currently, every "date" selected is returning a value of "2016" in "Mileage Cost."

    Case(Year([Date]),2015,([Mileage]*0.575),Year([Date]),2016,([Mileage]*0.54),Year([Date]),2017,([Mileage]*0.535),Year([Date]),2018,([Mileage]*0.545),null)

    Any ideas would be appreciated!



  • 2.  RE: case formula returning one value

    Posted 12-27-2018 20:37
    You don't need to repeat year([Date]) multiple times. 

    A simple 

    Case(Year([Date]),
    2015,result 1,
    2016, result 2,
    2017, result 3,
    ....)


  • 3.  RE: case formula returning one value

    Posted 12-27-2018 20:38
    Ya, that formula is a bit muddled.  You are over complicating it.

    Case(Year([Date]),
    2015,[Mileage]*0.575,
    2016,[Mileage]*0.54,
    2017,[Mileage]*0.535,
    2018,[Mileage]*0.545,
    null)


  • 4.  RE: case formula returning one value

    Posted 12-28-2018 14:59
    Thank you both! Works great!