Overdue date problem

gipson542

New Member
Joined
Jan 22, 2005
Messages
3
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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
Joined
Jan 22, 2005
Messages
3
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
Joined
Jan 22, 2005
Messages
3
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?
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top