Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Two Separate Date Fields into one sum

MaryAnn Dicks
edited 12/09/19 in Archived 2015 Posts

I need to give per diem to employers for days worked for two events.  Some will work one, some will work both.  I have a formula that works great if they are working both events, but get the #MISSING OR INVALID PARAMETERS" message when only one event is worked.  Does anyone know how I can get a column to return a "0" date?




  • MaryAnn Dicks
    edited 09/14/15

    For example:

    person A is working 10/15/15-11/01/15 for a total of 18 days

    Person A is working 11/5/15-11/08/18 for four additional days for a total of 22 days.

    Person B is only working 11/5/15-11/08-15 for a total of four days.  Because the fist event is not being worked I get an error message in the "Total days worked" column.


    The department only wants one line per person working.




  • Travis
    Travis Employee

    You might be able to use a SUMIF to calculate this. Could you provide a screenshot of your sheet (hiding any sensitive data) and show me the formula you are currently using? 

  • MaryAnn Dicks
    edited 09/16/15


    Right now I use them as two different events which causes problems if they only work one event I get the error messages shown above.






    per diem.JPG

  • Travis
    Travis Employee

    Thanks for the screenshot! Just to confirm, the issue is, when CC Arrival Date and CC Departure Dates are blank, you are getting an error, correct?


    Try this formula which will first check if both fields contain a date. If they both contain a date, then the formula will show the net days between them. If they do not both contain a date, the cell will remain blank.


    =IF(AND(ISDATE([CC Arrival Date]1), ISDATE([CC Departure Date]1)), NETDAYS([CC Arrival Date]1, [CC Departure Date]1), "")



  • Than you Travis.  That works, but I now receive an #INVALID DATA TYPE error in the Per Diem Total Column?



    invalid data.JPG

  • Travis
    Travis Employee

    MaryAnnm from your screenshot, it looks like the Daily Rate values is formatted as text, rather than a number (I can tell because it is left justified [text]. Numbers are right justified). If this is formatted as text then the error is caused you trying to multiply text and a number together. Do you have a formula in Daily Rate? If so, try removing the quotes from the number values. For example:


    If your formula looks like this:


    =IF([AS Days On Site]1 < 10, "$60.00")


    Change it to this:


    =IF([AS Days On Site]1 < 10, 60)


    This will format the result as a number (quotes makes it text). You can then format the cell as currency through the Currency Format button on your toolbar. 


    If this does not fix it, could you show me the formula in the PER DIEL TOTAL and Daily Rate cells?

This discussion has been closed.