Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
getting future dates from an existing date
You are right, i am sorry it was my mistake
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation of future dates | Excel Discussion (Misc queries) | |||
Dates are saved with a future date | New Users to Excel | |||
future dates | Excel Worksheet Functions | |||
future dates | Excel Worksheet Functions | |||
How can I hide points for future dates on a Year to Date chart? | Charts and Charting in Excel |