Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Specific date in Biwwekly Based on date
I am trying to find the next biweekly end date based on todays date.
The biweeklies end on Saturdays and that is the date I need to return. 1-24-09, 1-10-09, 12-27-08. These are some of the dates. Is there a way to do this with functions in excel or is this going to have to be a UDF? Thanks in advance, Jay |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Specific date in Biwwekly Based on date
Try this
=IF(MOD(WEEKNUM(A1),2)=1,A1+CHOOSE(WEEKDAY(A1),13, 12,11,10,9,8,7),A1+CHOOSE(WEEKDAY(A1),6,5,4,3,2,1, 0)) Mike "jlclyde" wrote: I am trying to find the next biweekly end date based on todays date. The biweeklies end on Saturdays and that is the date I need to return. 1-24-09, 1-10-09, 12-27-08. These are some of the dates. Is there a way to do this with functions in excel or is this going to have to be a UDF? Thanks in advance, Jay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Specific date in Biwwekly Based on date
Put a date in F2 and in G2 use
=F2+IF(WEEKDAY(F2)=7,14,7)-WEEKDAY(F2)+(MOD(F2+IF(WEEKDAY(F2)=7,14,7)-WEEKDAY(F2)-DATE(2008,12,27),14)<0)*7 Now the first part of this =F2+IF(WEEKDAY(F2)=7,14,7)-WEEKDAY(F2) finds the date of the next Saturday The second part +(MOD(F2+IF(WEEKDAY(F2)=7,14,7)-WEEKDAY(F2)-DATE(2008,12,27),14)<0)*7 adds 7 if the next Saturday date is not an even number of 14 days away from 27/Dec/2008 Test this with various dates in F2; it works for me Now replace F2 by TODAY() =TODAY()+IF(WEEKDAY(TODAY())=7,14,7)-WEEKDAY(TODAY())+(MOD(TODAY()+IF(WEEKDAY(TODAY())= 7,14,7)-WEEKDAY(TODAY())-DATE(2008,12,27),14)<0)*7 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "jlclyde" wrote in message ... I am trying to find the next biweekly end date based on todays date. The biweeklies end on Saturdays and that is the date I need to return. 1-24-09, 1-10-09, 12-27-08. These are some of the dates. Is there a way to do this with functions in excel or is this going to have to be a UDF? Thanks in advance, Jay |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Specific date in Biwwekly Based on date
Both work great. Thank you for the ideas. I always seem to forget
about MOD. Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
business day date from a specific date based on a number of days | Excel Worksheet Functions | |||
Find specific date for day in week | Excel Discussion (Misc queries) | |||
Formula to subtract a $ value based on specific date | Excel Worksheet Functions | |||
How do I set up alarms based on days from a specific date? | Excel Worksheet Functions | |||
adding specific cells based on a corresponding date | Excel Worksheet Functions |