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?

  • Is your StartDate column set to dateonly or datetime? – Hardik Jun 6 '16 at 17:48
  • ahh start date IS date and time. I should change that to just date, right? – mbeck Jun 6 '16 at 18:00
  • Yes... Please check my answer – Hardik Jun 6 '16 at 18:01
  • wait.. I was wrong. Both StartDate and NewDate are set to date only – mbeck Jun 6 '16 at 18:01
  • Your calculated column's return type must be datetime – Hardik Jun 6 '16 at 18:02
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))
  • 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 '16 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 '16 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 '16 at 18:12
  • Use updated formula from answer – Hardik Jun 6 '16 at 18:17
  • Did you get it working? – Hardik Jun 6 '16 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, privacy policy and cookie policy

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