How to get DATEDIF function to show negative numbers?

Question

I have this function:

=IF(I44="N/A";"";IF(O44="Completed";"0";IF(I44<>"";DATEDIF(TODAY();I44;"d");"")))

When the days goes to a negative number the cell will show the value: #NUM!

I44 is my deadline and I would like to know how many days this task is overdue.

Answer 1

Dear respected Curious_George1993,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, you can use a workaround to display negative numbers by modifying your formula as follows: =IF(I44="N/A";"";IF(O44="Completed";"0";IF(I44<>"";IF(TODAY()>I44;DATEDIF(I44;TODAY();"d");-DATEDIF(TODAY();I44;"d"));"")))

Or you can modify your formula to display negative numbers:(In this modified formula, instead of using the DATEDIF function, we subtract the deadline (I44) from today's date (TODAY()).

=IF(I44="N/A";"";IF(O44="Completed";"0";IF(I44<>"";I44-TODAY();"")))

If there is any misunderstanding, I apologize and please feel free to post back to us with more detailed information for better understanding and guiding you further.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad

Answer 2

You don't need DATEDIF here:

=IF(I44="N/A";"";IF(O44="Completed";"0";IF(I44<>"";I44-TODAY();"")))

Format the cell with the formula as General or as Number.

Answer 3

Hi respected Curious_George1993,

I'm writing a follow up this case, and I haven’t received any information from you, may I know have you checked above reply? Feel free to post back if you need further assistance.

Have a good day and stay safe 😊

Sincerely,

Waqas Muhammad

Answer 4

Hi,

It worked perfectly.

Thank you for the help.

Answer 5

Hi respected Curious_George1993,

Thanks for your valuable feedback and thanks for sharing updates with us.

I appreciate your kind words.

If you find our suggestions helpful, you can also use the feedback tool below our reply to submit your feedback for us (pictured below), which will definitely help others in the community who are experiencing similar problems to find their problems more quickly.

If there is any issue related with the Office applications in the future, please feel free to post back in this community.

We will be here for help.

Have a good day and stay safe 😊

Best wishes

Waqas Muhammad