Another Date formula help question

Darth_Sysad

New Member
I am tracking some minor training. Column A is a list of usernames, Column B is for the date accomplished, and column C is where I would like the training due date to show up. Sometimes the training is due in 6 months, sometimes 1 year, sometimes 3 years.

I would also like it to say "DUE NOW" if the training is over due.

I have this set up in an access database for my heavy duty training stuff but the formulas are not the same.

Can someone please help?

Thanks,

James

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Welcome to MrExcel Board!

So if a particular user hasn't done that training yet, Column B is blank?

Darth_Sysad said:
Sometimes the training is due in 6 months, sometimes 1 year, sometimes 3 years.

How can you tell the difference? It seems like some info is missing here -- that or I'm just not understanding how you arrive at the due date calculation.

Correct. If the Column B cell is blank, the user hasn't accomplished the training yet, so therefore he doesn't have a due date yet.

To make it easier, we can just assume that all training is annual.

Does this help?

But you need a date to count the six months (or 1 year, or whatever it will be) from. Would that be whenever you enter the person on the sheet? Do you get what I mean?

ADVERTISEMENT
I enter a persons name into column A then the date they accomplished the training in column B.

USERNAME DATE ACCOMPLISHED DUE DATE

Doe, John Q 15-Mar-05 15-Mar-06
Doe, Jane P 20-Feb-04 DUE NOW

I want the due date to say 15-Mar-06 for John Doe because it isn't Mar 06 yet and DUE NOW for Jane Doe because the accomplished date was more than a year old.

Does this make more sense?

How about this?:
Book1
ABCD
1USERNAMEDATE ACCOMPLISHEDDUE DATE
2
3Doe, John Q15-Mar-0515-Mar-06
4Doe, Jane P20-Feb-04DUE NOW
Sheet3

Formula in C3 is: =IF(DATEDIF(B3,TODAY(),"y")>=1,"DUE NOW",B3+365)

and copy down. Did I get everything in there? Seems like I missed a part of the condition. Check it out and post back. Hope that helps!

It works. Great. Thanks for the help Taz

You're welcome. Glad you got it working.

Replies
4
Views
175
Replies
15
Views
685
Replies
9
Views
368
Replies
12
Views
262
Replies
9
Views
290

Threads
1,196,054
Messages
6,013,126
Members
441,749
Latest member
batel19

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

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