Calculating between dates using if functions

[mayflower09]

Hi,

Trying to add dates depending on if meets criteria. If the date is less than 7 days then I want the function to go to the next Thursday before. If it is more than 7 days, then it can leave on that Thursday date.

Here's the equation I have: =IF((L3-WEEKDAY(L3+3)-6)<8,L3-14,IF((L3-WEEKDAY(L3+3)-6)>8,N3))

However, it gives back "1/0/1900" so don't know how to fix. My L3 equation is- =IF(OR(WEEKDAY(K3)=1,WEEKDAY(K3)=7),K3-WEEKDAY(K3+1),K3)

And lastly for K3 I put an equation to add 30 days from the stamp date that I manually type in J3.

Thanks!!!!

 

[63falcondude]

1/0/1900 changed to Number format is 0.

Please create a small sample of your data along with the desired output of the formula. This is much more helpful for us than a non-working formula.

 

[cerfani]

not sure what you are trying to do exactly as i am not following your explanation but it appears you have a formatting issue... just format the "1/0/1900" as a number or use general formatting

 

[mayflower09]

Thank you for your fast response! Basically date received is date I put in, goal date is just the cell#+30. Meeting potential I want to do 1 week before the goal date, but if it falls on a Thursday then I want to do it the week before that date. So for example 6/1 falls on a Thursday so I want to push back to 5/25.

Here are my equations: =IF(OR(WEEKDAY(K2)=1,WEEKDAY(K2)=7),K2-WEEKDAY(K2+1),K2) for meeting potential
Meeting date- =IF((L3-WEEKDAY(L3+3)-6)<8,L3-14,IF((L3-WEEKDAY(L3+3)-6)>8,N3))

L3 is goal date

Here is the data:
[TABLE="class: cms_table, width: 496"]
<tbody>[TR]
[TD][/TD]
[TD]date received [/TD]
[TD]Goal Date [/TD]
[TD]Meeting Potential [/TD]
[TD]Meeting Date [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/9/2017[/TD]
[TD]6/8/2017[/TD]
[TD]6/1/2017[/TD]
[TD]5/25/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[cerfani]

Meeting Potential:

=L3-IF(WEEKDAY(L3-7)=4,14,7)

you explained Meeting potential and that is what I translated... you essentially said... if goal date - 7 is a thursday then the potential is goal day -14

but if it falls on a Thursday then I want to do it the week before that date

means 2 weeks or 14 from goal date

You never explained Meeting Date to me

 

[mayflower09]

My apologies. Meeting date is the actual date to meet but it must fall on a Thursday. It's similar to meeting date potential so you can ignore meeting potential. I just had meeting potential since I didn't know how to have excel do it all in one equation, so I used meeting date to calculate from meeting potential that if the day is 7 week days or less then needs to be pushed back to the Thursday before.

Your equation works! But it makes the date fall on a Wednesday, I need to fall on a Thursday. Basically we have meetings on Thursday but we want to make sure there is enough time in between (at least 7 week days). If less than 7 week days (not including the weekends) then it must be put back on the Thursday before to allow more time for people to do their job to give 7 week days. Does this help? Thanks again!!!