Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 51
Default getting future dates from an existing date

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.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,805
Default getting future dates from an existing date

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.
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Totti" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 964
Default getting future dates from an existing date

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

"Totti" wrote in message
...
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.



  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,805
Default getting future dates from an existing date

For first Mon of next month
enter in B1
=DATE(IF(MONTH(A1)=12,YEAR(A1)+1,YEAR(A1)),MONTH(A 1)+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,MON TH(A1)+2),1)
Try to combine these two to get the remaining results...


--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Sheeloo" wrote:

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.
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Totti" wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default getting future dates from an existing date

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(A 1),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


"Totti" wrote in message
...
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.





  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 964
Default getting future dates from an existing date

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

"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
For first Mon of next month
enter in B1
=DATE(IF(MONTH(A1)=12,YEAR(A1)+1,YEAR(A1)),MONTH(A 1)+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,MON TH(A1)+2),1)
Try to combine these two to get the remaining results...


--
Always provide your feedback so that others know whether the solution
worked
or problem still persists ...


"Sheeloo" wrote:

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.
--
Always provide your feedback so that others know whether the solution
worked
or problem still persists ...


"Totti" wrote:

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.



  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 51
Default getting future dates from an existing date

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

  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,805
Default getting future dates from an existing date

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,
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Peo Sjoblom" wrote:

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

  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 964
Default getting future dates from an existing date

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

"Totti" wrote in message
...
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


  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default getting future dates from an existing date

Try

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

HTH,
Bernie
MS Excel MVP


"Totti" wrote in message
...
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




  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 964
Default getting future dates from an existing date

That will return the same as the formula I gave Totti

you can leave out the 1

--


Regards,


Peo Sjoblom

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Try

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

HTH,
Bernie
MS Excel MVP


"Totti" wrote in message
...
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




  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 964
Default getting future dates from an existing date

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

"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
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,
--
Always provide your feedback so that others know whether the solution
worked
or problem still persists ...


"Peo Sjoblom" wrote:

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



  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 51
Default getting future dates from an existing date

You are right, i am sorry it was my mistake
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculation of future dates Michael Excel Discussion (Misc queries) 2 September 9th 08 09:44 AM
Dates are saved with a future date Eates-a-Lot New Users to Excel 2 July 22nd 05 02:25 PM
future dates kevrgallagher Excel Worksheet Functions 2 July 14th 05 05:36 PM
future dates kevrgallagher Excel Worksheet Functions 4 July 12th 05 03:47 PM
How can I hide points for future dates on a Year to Date chart? rlmills Charts and Charting in Excel 1 November 29th 04 06:23 PM


All times are GMT +1. The time now is 04:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"