Date calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry, guys, but I'm back with another question
Here's my dilemma:
Cell C25= 6/5/2004
Cell C24= 3/31/200
Now, I want cell c26 to be the actual date of C25-53 days but that it should fall on a Tuesday only

I was using the formula that was offered by one of you kind souls as follows:
=MIN(C24,C25-53)-WEEKDAY(MIN(C24,C25-53)-1,3
but I was getting 3/30/04 as the result and that's when I realized MY mistake in explaining it to the newsgroup. It's not the minimum of the two, it's the closest to the 53 days on a Tuesday from cell C25. I tried eliminating the MIN portion from the formula but I just get an error message

Can you help me again, please
thanks...
 
I forgot to mention that I still need to exclude the date list of holidays (A2:A24 found on sheet 2).

Patiently waiting.....
 
Sorry, guys, but I'm back with another question!
Here's my dilemma:
Cell C25= 6/5/2004
Cell C24= 3/31/2004
Now, I want cell c26 to be the actual date of C25-53 days but that it should fall on a Tuesday only.

I was using the formula that was offered by one of you kind souls as follows:
=MIN(C24,C25-53)-WEEKDAY(MIN(C24,C25-53)-1,3)
but I was getting 3/30/04 as the result and that's when I realized MY mistake in explaining it to the newsgroup. It's not the minimum of the two, it's the closest to the 53 days on a Tuesday from cell C25. I tried eliminating the MIN portion from the formula but I just get an error message.
Click to expand...

I believe the *closest* Tuesday to the date can be calculated by the formula:

=$C$25-B1-WEEKDAY($C$25-B1-3)+7-MOD(ROUND(WEEKDAY($C$25-B1-2)/3,0),2)*7

where the number of days to subtract from C25 is in B1.
I still need to exclude the date list of holidays
Click to expand...

Well, what exactly do you want to happen if the calculated closest Tuesday is a
holiday??

Go to the next non-holiday?
Go to the preceding non-holiday?
Go to the next Tuesday that is not a holiday?
Go to the preceding Tuesday that is not a holiday?
Something else????


--ron
 
Go to the next Tuesday that is not a holiday, please

----- Ron Rosenfeld wrote: ----

On Mon, 1 Dec 2003 08:21:22 -0800, "Marcy
Sorry, guys, but I'm back with another question
Here's my dilemma:
Cell C25= 6/5/2004
Cell C24= 3/31/200
Now, I want cell c26 to be the actual date of C25-53 days but that it should fall on a Tuesday only
=MIN(C24,C25-53)-WEEKDAY(MIN(C24,C25-53)-1,3
but I was getting 3/30/04 as the result and that's when I realized MY mistake in explaining it to the newsgroup. It's not the minimum of the two, it's the closest to the 53 days on a Tuesday from cell C25. I tried eliminating the MIN portion from the formula but I just get an error message
Click to expand...


I believe the *closest* Tuesday to the date can be calculated by the formula

=$C$25-B1-WEEKDAY($C$25-B1-3)+7-MOD(ROUND(WEEKDAY($C$25-B1-2)/3,0),2)*

where the number of days to subtract from C25 is in B1
I still need to exclude the date list of holiday
Click to expand...

Well, what exactly do you want to happen if the calculated closest Tuesday is
holiday?

Go to the next non-holiday
Go to the preceding non-holiday
Go to the next Tuesday that is not a holiday
Go to the preceding Tuesday that is not a holiday
Something else???


--ro
 
Back
Top