Another Date formula help question

Darth_Sysad

New Member
Joined
Nov 9, 2005
Messages
4
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,430
Messages
6,172,053
Members
452,444
Latest member
ShaImran193

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
Back
Top