Forum Discussion
Nested IF and DATEDIFF formulas when some date fields are blank
I would like to count the number of days between two date fields: 'datereceived' and 'datedulymade'. I tried to use the DATEDIFF function, BUT - some of the corresponding dates are the same (e.g. number of days between dates=0) which seems to cause an error, ALSO, some of the 'datedulymade' rows are blank (i.e. process not complete)
I have tried to nest an IF statement in to the DATEDIFF field however the blank date fields seem to be causing an error of the format type and I I am unsure how to get around this.
6 Replies
- Nhallquist
Helper V
Can you post the formula's that you have tried?
- Greg_Deckler
Community Champion
Perhaps use ISBLANK in your IF statement to check for that boundary condition?
https://msdn.microsoft.com/en-us/library/ee634204.aspx
SeanNRW_admin1 You can use DATEDIFF when the dates are the same (see picture below) but you can't have blanks or negative values (to deal with negatives see link at the bottom)
Num of Days = IF ( ISBLANK ( 'Table'[Made] ), BLANK (), DATEDIFF ( 'Table'[Received], 'Table'[Made], DAY ) )Also see response here... (although this doesn't deal with Blank may give some ideas)
Problem solved, combined with guidance in the link. Many thanks :)
