0

I have two fields, StartDate and NewDate. The NewDate field, I want to calculate based on StartDate and return a date.

If the Start date is greater than or equal to 3/31/2016, then enter 4/26/2017 in NewDate field. If not, then leave the field blank.

=IF([StartDate]>="3/31/2016","4/26/2017","01/01/2010")

I have read that you can't leave the field blank, so I stuck 1/1/2010 in there to get around that issue, for now.

But, this formula still doesn't work. I don't know what I'm doing wrong. The NewDate field shows up blank (and I thought it couldn't). I made sure the date results are set for a Date. I edited my start date all sorts of ways to see what happens, and it's always blank.

Help?

5
  • Is your StartDate column set to dateonly or datetime?
    – Hardik
    Jun 6, 2016 at 17:48
  • ahh start date IS date and time. I should change that to just date, right?
    – mbeck
    Jun 6, 2016 at 18:00
  • Yes... Please check my answer
    – Hardik
    Jun 6, 2016 at 18:01
  • wait.. I was wrong. Both StartDate and NewDate are set to date only
    – mbeck
    Jun 6, 2016 at 18:01
  • Your calculated column's return type must be datetime
    – Hardik
    Jun 6, 2016 at 18:02

2 Answers 2

0

Please check below options first.

  1. StartDate of type Datetime with date only option.
  2. Choose The data type returned from your formula must be DateTime.

Than Below formula.

=IF([StartDate]>=Date(2016,03,31),Date(2017,04,26),Date(2010,01,01))
13
  • StartDate is Date only. Return data type is Date and Time formatted as Date Only. I changed the return format to Date & Time and it still didn't work.
    – mbeck
    Jun 6, 2016 at 18:05
  • return type for the calculated column must be date time. Check here...nbsp.blob.core.windows.net/uploads/Photos/eusp/…
    – Hardik
    Jun 6, 2016 at 18:09
  • Yes. That's what StartDate and NewDate are both set to. The formula returns 1/1/2010 now, no matter what the StartDate is.
    – mbeck
    Jun 6, 2016 at 18:12
  • Use updated formula from answer
    – Hardik
    Jun 6, 2016 at 18:17
  • Did you get it working?
    – Hardik
    Jun 6, 2016 at 18:30
1

You have to compare a date to a date, not a string. Try this:

=IF([StartDate]>DATE(2016,06,01),"4/26/2017","")

Dates can't be set blank through a workflow. It worked fine for me with a calculated field. This also worked fine for me for a "Date and Time" field... it doesn't have to be date only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.