Two Separate Date Fields into one sum

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?

Thanks,

Find more posts tagged with

Finance

Formulas

Comments

MaryAnn Dicks

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

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

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.

Thanks,

per diem.JPG

Travis

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), "")

MaryAnn Dicks

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

invalid data.JPG

Travis

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?