OT: Excel Help

Ragnarok · #1 August 27th, 2004, 05:34 PM

I don't know if we have any excel experts here but I am seeking some help with an excel formula.

I have two cells, one will be named Check Out and the other Check In. I need a formula that will check the Check Out cell to see if it has been longer then 6 months since that date if the Check In cell is empty. Is this possible in excel?

Edit: Forgot to mentioned I searched the web before posting here but I could not find anything.

Thanks for your help!

Rasorow · #2 August 27th, 2004, 06:07 PM

Yes, it should be possible. Just to recap:

Check out Check in Greater then 6 months
6/24/03 1/28/04 yes

The above is the table you have. You want to automate the calculation of the third col to tell you if it has been greater then 6 months. I believe you can do this 1 of 2 ways. You can write a VB script through Excel's VB interface or use an IF statement for your formula. Try IF(DATE(A3,B3,120)>D3,"No","Yes") where cell A3 is the year and cell B3 is the month. If you need it down to the specific day look at the Day function.

Rasorow

geoschmo · #3 August 27th, 2004, 06:32 PM

I would say the easiest way to do it is with a nested IF formula. The exact formula would depend on how you figure the 6 month interval. If you go with 180 days = 6 months, the formula is pretty simple:

=IF(B3="",IF(TODAY()-A3>180,"yes","no"),"no")

where colum B is your Checkout dates and Column A is your CheckIn dates

If you want to use 6 months on the Calendar regardless of number of days, it gets more complicated. It can still be done with one nested IF formula, but you'd need three IF's instead of two because you use the difference in months if todays day of the month is greater then the check out day of the month, and if it's not greater, you use the difference in months - 1. I can work that up tonight and post it if you'd like.

Ragnarok · #4 August 27th, 2004, 06:48 PM

I am having trouble getting it set up. I may not understand fully how I need to set up my cells to get it to work correctly.

Say I have cell A1 with just the text "Check out" and then cell B1 with the text "Check in."

Next I have cell A2 with a date of 6/30/2004 and cell B2 with a date of 2/15/2005. where would I put the formula?

geoschmo · #5 August 27th, 2004, 07:23 PM

Wherever you'd want to put it that isn't going to have some other data. The most likely spot would be C2.

And the formula would be

=IF(B2="",IF(TODAY()-A2>180,"yes","no"),"no")

Or are you wanting it to list the actual number of months instead of yes or no that it's over 6 months?

Ragnarok · #6 August 30th, 2004, 04:25 PM

Thanks Geo! That seemed to do the trick. I had to toy around with it a bit just to understand what it was doing but your formula worked.