# Overdue date problem

#### gipson542

##### New Member
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.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Tazguy37

##### MrExcel MVP
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

##### New Member
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

##### New Member
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?

Replies
4
Views
375
Replies
2
Views
381
Replies
4
Views
236
Replies
2
Views
66
Replies
9
Views
141

1,181,752
Messages
5,931,813
Members
436,805
Latest member
waseem abbas

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back