getting future dates from an existing date

T

Totti

Hi everyone,
I have an excel sheet filled with dates of this year and few future
years, i am intending to use it as a worksheet for knowing some prcise
dates and put precise job for these days, this part is obvious i ll
use the conditional formatting and LOOKUP functions for the jobs. the
hard part for me is in finding 3 dates in 3 columns

col 1 -- Next Thursday (if date is a Thursday then the date itself)
col 2 -- The first Friday of the next month
col 3 -- The first Monday of the next even month (Feb, Apr, Jun, Aug,
Oct, Dec)

Actually i will be using these specific days for specific jobs(col 1)
or payments (col 2 & 3)
my dates are in col 0 and they are formatted as "dd.mm.yy"

I know i am asking for too much, and i appreciate any help in these
tasks.
 
S

Sheeloo

If your date is in A1 then
=IF(WEEKDAY(A1,1)>5,A1+12-WEEKDAY(A1,1),A1+5-WEEKDAY(A1,1))
will give you the date of next Thur (or today if it is Thu). Format the cell
with the formula as a date..

I will give the other formulae after some time.
In the meantime, you can try on your own.
 
P

Peo Sjoblom

With your dates in A1



Next Thursday


=A1+7-WEEKDAY(A1+2)



First Friday of next month is


=DATE(YEAR(A1),MONTH(A1)+1,0)+8-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)+2)



First Monday next even months


=DATE(YEAR(A1),CEILING(MONTH(A1)+1,2),0)+4-WEEKDAY(DATE(YEAR(A1),CEILING(MONTH(A1)+1,2),0)+2)




--


Regards,


Peo Sjoblom
 
S

Sheeloo

For first Mon of next month
enter in B1
=DATE(IF(MONTH(A1)=12,YEAR(A1)+1,YEAR(A1)),MONTH(A1)+1,1) to get first day
of next month
and in C1
=IF(WEEKDAY(B1,1)>2,B1+9-WEEKDAY(B1,1),B1+2-WEEKDAY(B1,1)) to get the next Mon

Use this to get first day of next even month
=DATE(YEAR(A1),IF(MOD(MONTH(A1),2),MONTH(A1)+1,MONTH(A1)+2),1)
Try to combine these two to get the remaining results...
 
B

Bernie Deitrick

Totti,


With the date in A1:

The first Friday of the next month:
=DATE(YEAR(A1),MONTH(A1)+1,1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)+1)

The first Monday of the next even month:
=DATE(YEAR(A1),MONTH(A1)+IF(MOD(MONTH(A1),2)=0,2,1),1+((1-(2>=WEEKDAY(DATE(YEAR(A1),MONTH(A1)+IF(MOD(MONTH(A1),2)=0,2,1),1))))*7)+(2-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+IF(MOD(MONTH(A1),2)=0,2,1),1))))

HTH,
Bernie
MS Excel MVP
 
P

Peo Sjoblom

I don't think those formulas will work for the OP,
he didn't ask for the first day of the next month he asked
for the first Friday of the next month and the first Monday of the next even
month

--


Regards,


Peo Sjoblom
 
T

Totti

Dear, Peo Sjoblom

Thank you a lot first of all for :

First Friday of next month is

=DATE(YEAR(A1),MONTH(A1)+1,0)+8-
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)+2)

First Monday next even months

=DATE(YEAR(A1),CEILING(MONTH(A1)+1,2),0)+4-
WEEKDAY(DATE(YEAR(A1),CEILING(MO­NTH(A1)+1,2),0)+2)

they are working amazingly, but
Next Thursday

=A1+7-WEEKDAY(A1+2)

is not, i even put on the next row,
=TEXT(DATE(YEAR(E2),MONTH(E2),DAY(E2)),"Dddd")
to check if the day is thursday and i see they are not! and i doubled
check my formula fo rgetting the day with the 2 solutions you gave me
which i said they are working and i saw they are all fridays or all
mondays and this is what i want. but as long as next thursday is
concerned. unfortunately not! it is not
 
S

Sheeloo

Thanks Peo for pointing that out...

Pl. note that my attempt is to help the person asking the question to
provide enough pointers so that he or she is able to understand the solution
and build on it.

Though not as clean as your solution, I had provided enough building blocks
so that the solution could be completed.

I do not believe we are here as free help to do the work for others, rather
we are here to provide guidance and help.

Regards,
 
P

Peo Sjoblom

Works fine for me, if you have a legit date in A1 that formula will return
next Thursday unless the date is a Thursday
then it will return that particular date, example

10/13/2008 Mon
10/14/2008 Tue
10/15/2008 Wed
10/16/2008 Thu
10/17/2008 Fri
10/18/2008 Sat
10/19/2008 Sun

with the above values in A1:A7


my formula copied down will return


10/16/2008 Thu
10/16/2008 Thu
10/16/2008 Thu
10/16/2008 Thu
10/23/2008 Thu
10/23/2008 Thu
10/23/2008 Thu

and those are Thursdays

--


Regards,


Peo Sjoblom

Dear, Peo Sjoblom

Thank you a lot first of all for :

First Friday of next month is

=DATE(YEAR(A1),MONTH(A1)+1,0)+8-
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)+2)

First Monday next even months

=DATE(YEAR(A1),CEILING(MONTH(A1)+1,2),0)+4-
WEEKDAY(DATE(YEAR(A1),CEILING(MO­NTH(A1)+1,2),0)+2)

they are working amazingly, but
Next Thursday

=A1+7-WEEKDAY(A1+2)

is not, i even put on the next row,
=TEXT(DATE(YEAR(E2),MONTH(E2),DAY(E2)),"Dddd")
to check if the day is thursday and i see they are not! and i doubled
check my formula fo rgetting the day with the 2 solutions you gave me
which i said they are working and i saw they are all fridays or all
mondays and this is what i want. but as long as next thursday is
concerned. unfortunately not! it is not
 
B

Bernie Deitrick

Try

=A1+7-WEEKDAY(A1+2,1)

HTH,
Bernie
MS Excel MVP


Dear, Peo Sjoblom

Thank you a lot first of all for :

First Friday of next month is

=DATE(YEAR(A1),MONTH(A1)+1,0)+8-
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)+2)

First Monday next even months

=DATE(YEAR(A1),CEILING(MONTH(A1)+1,2),0)+4-
WEEKDAY(DATE(YEAR(A1),CEILING(MO­NTH(A1)+1,2),0)+2)

they are working amazingly, but
Next Thursday

=A1+7-WEEKDAY(A1+2)

is not, i even put on the next row,
=TEXT(DATE(YEAR(E2),MONTH(E2),DAY(E2)),"Dddd")
to check if the day is thursday and i see they are not! and i doubled
check my formula fo rgetting the day with the 2 solutions you gave me
which i said they are working and i saw they are all fridays or all
mondays and this is what i want. but as long as next thursday is
concerned. unfortunately not! it is not
 
P

Peo Sjoblom

That will return the same as the formula I gave Totti

you can leave out the 1

--


Regards,


Peo Sjoblom
 
P

Peo Sjoblom

Sorry, that doesn't make any sense to me.

There is one thing if someone asks "what does the VLOOKUP" function do?
And you refer them to help or tell them to post a specific question but if I
ask
how can I get the average of the last 10 cells in a range where another
range is "x" and you give me an answer
showing how you get the average of the first 2 cells in a range where
another range is "y"?
That doesn't make any sense to me at least.


--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Help with dates 2
finding last and creating average amount income to date 1
problem with date in the VBA macro 0
Excel Display certain dates 9
Averaging many non-contiguous cells if they are blank 1
projecting dates 8
Ageing dates 2
VBA Go to Today's Date 4

Top