Forum Discussion

NipawanV's avatar
NipawanV
Icon for Helper I rankHelper I
4 years ago
Solved

Replace two blank date fields with another date column

Hello, I created column formula called "Date3" to replace with date field in the other columns.  Every line went well give the correct results but on line 6 I used "AND" & "BLANK() to check two date fields if both are blank, it should return modified date.  But it's empty.  Here is the formula:

 

Date3 =
SWITCH(
TRUE(),
[Due Date (scheduledend)] = BLANK(), [Actual End Date],
[Actual End Date] = BLANK(), [Due Date (scheduledend)],
AND([Due Date (scheduledend)], [Actual End Date]) = BLANK(), activitypointers[modifiedon],
[Actual End Date]
)

 

Could you kindly advise what wrong in the formula ? 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi NipawanV ,

     

    I have a test by your code, I and I could reproduce your problem. I think your issue should be caused that your conditional statement will go [Due Date (scheduledend)] = BLANK(), [Actual End Date] firstly, then it will return blank , there is something wrong in your And() function.

    Try this code.

    Date3 = 
IF (
    AND ( [Due Date (scheduledend)] = BLANK (), [Actual End Date] = BLANK () ),
    activitypointers[modifiedon],
    IF (
        [Due Date (scheduledend)] = BLANK (),
        [Actual End Date],
        IF (
            [Actual End Date] = BLANK (),
            [Due Date (scheduledend)],
            [Actual End Date]
        )
    )
)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    NipawanV , Try like

    coalesce([Due Date (scheduledend)], [Actual End Date],[Due Date (scheduledend)],activitypointers[modifiedon])

    • NipawanV's avatar
      NipawanV
      Icon for Helper I rankHelper I
      4 years ago

      amitchandak Many thanks but it doesn't work.  Apply the said line it doesn't show the result from line 7 as well. Line 7 in my formula if "Actual End Date" and "Activity Due Date " should pickup the result from "Actual End Date".  Now it's disappear.

      See result in Date4 that applied your formula. (I used "created on" for the time being as it's the text field.  coalesce() doesn't allow me to use "modified on" which is the date field.) it doesn't return any date at all. 

      Any more hints, please!! 

       




      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi NipawanV ,

         

        I have a test by your code, I and I could reproduce your problem. I think your issue should be caused that your conditional statement will go [Due Date (scheduledend)] = BLANK(), [Actual End Date] firstly, then it will return blank , there is something wrong in your And() function.

        Try this code.

        Date3 = 
IF (
    AND ( [Due Date (scheduledend)] = BLANK (), [Actual End Date] = BLANK () ),
    activitypointers[modifiedon],
    IF (
        [Due Date (scheduledend)] = BLANK (),
        [Actual End Date],
        IF (
            [Actual End Date] = BLANK (),
            [Due Date (scheduledend)],
            [Actual End Date]
        )
    )
)

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.