PDA

View Full Version : [SOLVED] calculate date after 34 days and if attended clinic



emgerson
07-05-2015, 08:21 PM
Hi,
I'm looking at entering a date for a review of patient case, if they attend , then if they were seen on Friday than calculate a date 34 days later (they're case will be reviewed 4 weeks and 6 days after they had attended).
If they were seen on a Tuesday, they will be reviewed after 30 days.
the cell needs to show the date of review.
Example - if they are seen (I put in "attended") on 10/07/15, they will be reviewed 13/08/15.
If they are seen (again "Attended") on a Tuesday return a date that is 30 days later.
if attended not entered, leave blank.
Help please :)
Emily

vcoolio
07-06-2015, 12:52 AM
Hello Emily

The following formula may be all that you need:-


=IF(B2="","",IF(D2="Tuesday",C2+30,IF(D2="Friday",C2+34,"")))

You can drag it down as far as you need. It relates to Columns A:E with the formula in Column E.

I don't know what your spread sheet looks like so I've attached a mock-up spread sheet for you to play with. Click on a cell in Column E to see the formula displayed in the function bar. I've introduced a "helper" column(D) to extract the week day name from the dates in Column C. If "Attended" is not entered in the Status Column(B) then nothing happens in Column E. Have a play with Columns B & C to see how it works.

The helper column uses this formula:-


=TEXT(C2,"dddd")

dragged down.

I hope that this helps.

Cheerio,
vcoolio.

emgerson
07-09-2015, 12:48 PM
Thank you, I'll try it now and let you know.
Emily

emgerson
07-09-2015, 01:11 PM
Hi again, I seem to get a date in excel formatting, it looks like this 42250, 42257 etc.
How do i change it to look like a date?
Thanks for your help.

vcoolio
07-09-2015, 05:10 PM
Hello Emily,

You'll need to format the date column to "Date". High-light the whole column, right click and select "Format Cells" from the menu and then select "Date". From there, select the date format that suits your region.

Cheerio,
vcoolio.

emgerson
07-09-2015, 06:44 PM
Many thanks :)

vcoolio
07-09-2015, 11:40 PM
Hello Emily,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.

emgerson
07-15-2015, 06:12 PM
Hi again,
the formula is working great, Hoever I need it to retiurn a date only if it says Attended, so only for people that actually showed up for thei appointments, and remain blank if it says anything else,
Any idea how to add that? this is how the formula looks now and the Attended will be in culumn N
=IF(N4="","",IF(X4="Tuesday",K4+30,IF(X4="Friday",K4+34,"")))

Emily

Aussiebear
07-15-2015, 07:19 PM
Try the following;

=IF(N4="Y",IF(OR(X4="Tuesday", K4+30),(X4= Friday", K4+34),"","")))

emgerson
07-15-2015, 07:46 PM
Hi, can it inclue the word "Attended" to give a date and ignore anything else such as "Booked" etc?

Aussiebear
07-17-2015, 03:08 AM
Swap Y for attended