formula to auto input a date

[spaciedawn]

Hi,
I am a nurse at a prison and I have close to 1200 patients that I have to keep up with different medical information. None of our records are electronic so needless to say I am trying to create a "manual" E-MAR..that being said, I am administering TB shots and then entering the date of the shot in one cell and I would like it if the cell next to it could automatically figure when the next shot has to be done. A TB shot is done every year. so far, I have been doing this manually, PLEASE, if anyone can help me with a formula for this I would be so grateful. Also, I should say I am kinda new to excel. Any help would be great!! Thanks in advance

 

[cstimart]

Hi,
I am a nurse at a prison and I have close to 1200 patients that I have to keep up with different medical information. None of our records are electronic so needless to say I am trying to create a "manual" E-MAR..that being said, I am administering TB shots and then entering the date of the shot in one cell and I would like it if the cell next to it could automatically figure when the next shot has to be done. A TB shot is done every year. so far, I have been doing this manually, PLEASE, if anyone can help me with a formula for this I would be so grateful. Also, I should say I am kinda new to excel. Any help would be great!! Thanks in advance

Here's an option...

Alt-F11
Insert Module
paste this code in

Function NextYear(cell As Range)
With cell
    NextYear = Format(DateAdd("yyyy", 1, .Value), .NumberFormat)
End With
End Function

Then if you enter a date in cell A4, use the following in cell B4
=NextYear(A4)

You can then copy that formula to whatever cell(s) you need.

 

[Smitty]

Welcome to the Board!

Here's another option borrowing on cstimart's function. It's a change event, which will automatically enter both the current date and next year's due date automatically whenever you make a change in column B, so it reduces a few steps for you. You can adjust it for whatever column you want to cause it to trigger.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("B:B")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Target<br>                .Offset(, 1).Value = <SPAN style="color:#00007F">Date</SPAN><br>                .Offset(, 2).Value = DateAdd("yyyy", 1, Date)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

If you need an EMR application, check out: http://www.biosoftworld.com/

They have something called Medical Database Seven, which has a lite version that's free. It's all database driven and built with a license free version of Microsoft Access.

HTH,

 

[spaciedawn]

thanks for the hints... i am checking out that website now

 

[Smitty]

Sounds good. Let me know if they don't offer the free "lite" version anymore, and I'll send you a copy. Just PM me your e-mail address if you need it.