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
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,
Comments
-
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.
-
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?
-
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,
-
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?
-
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?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives