Overdue date problem

[gipson542]

I have a spreadsheet that sets up a training schedule. When I enter the hire date, the due date will be set according to the amount of training needed for that task. This is determined by a numerical value in column A.

=IF(A4="o",($B$2),IF(A4=1,($B$2+5),IF(A4=2,($B$2+30),IF(A4=3,($B$2+60),IF(A4=4,($B$2+90),B2)))))

This formula works fine.

There is also a column where I enter the date I started the training. This will tell me how many days overdue the task was and stop adding any more days.

The problem is the days overdue. Here is what I have in the "Days Overdue" column.

=IF(E4="",DATEDIF(D4,TODAY(),"d"),DATEDIF(D4,E4,"d"))

"E4" is in the "Training Started" column.

"D4" is in the "Due Date" column.

If the due date is greater than TODAY(), I get #NUM.
If I input a date after the future due date in column "E" it returns #VALUE.

If this is confusing I can send a scaled down version of the spreadsheet to someone.

 

[Tazguy37]

Maybe if you switch the order in your Days Overdue formula:

=IF(E4="",DATEDIF(TODAY(),D4,"d"),DATEDIF(E4,D4,"d"))

If the due date is in the past, you'll have to include an eventuality for that, too. Hope that helps some!

 

[gipson542]

When I changed the order of the DATEDIF I get a number value now. I forgot mention in the original message that I also want it to return a "0" value until TODAY() is greater than the due date. Then it should add days until a start date is entered, at which time it will stop adding days to the overdue column. I have been trying to do something with this formula.

=IF(E5="",DATEDIF(TODAY(),D5,"d"),IF(AND(D5>TODAY(),"0",DATEDIF(TODAY(),D5,"d")),DATEDIF(E5,D5,"d")))

I am sure there is something in there that I am missing but I am not the formula expert.

 

[gipson542]

I almost have it.

=IF(D4>TODAY(),AND(E4="","0",IF(D4<TODAY(),DATEDIF(D4,TODAY(),"d"),DATEDIF(D4,E4,"d"))))

This returns a false if the due date is the past and the task has not been started yet. How do I get it to add the days overdue until the task is started?