Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
I am trying to convert a specific date to a Week Ending value. The week
starts on Saturday and ends on Friday for this example. So.. any value from 6/14 through 6/20 would return a value of week ending 6-20-2008. Any suggestions? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
One way: for a date in A2, next Friday:
=CEILING(A2+1,7)-1 HTH Kostis Vezerides On Jun 20, 4:18 pm, drew wrote: I am trying to convert a specific date to a Week Ending value. The week starts on Saturday and ends on Friday for this example. So.. any value from 6/14 through 6/20 would return a value of week ending 6-20-2008. Any suggestions? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
try this formula. It assumes the date you want to convert (i.e. 6/14)
is in cell A14. It will always return the Friday following the date you enter. If you enter a Friday, it will return the same date. =+A14+7-IF(WEEKDAY(A14,1)=6,1,IF(WEEKDAY(A14,1)=7,2,WEEKDA Y(A14,1)+2)) On Jun 20, 9:18 am, drew wrote: I am trying to convert a specific date to a Week Ending value. The week starts on Saturday and ends on Friday for this example. So.. any value from 6/14 through 6/20 would return a value of week ending 6-20-2008. Any suggestions? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
=A1-WEEKDAY(A1+1,1)+7
-- Gary''s Student - gsnu200793 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
I like it!
-- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "vezerid" wrote in message ... One way: for a date in A2, next Friday: =CEILING(A2+1,7)-1 HTH Kostis Vezerides On Jun 20, 4:18 pm, drew wrote: I am trying to convert a specific date to a Week Ending value. The week starts on Saturday and ends on Friday for this example. So.. any value from 6/14 through 6/20 would return a value of week ending 6-20-2008. Any suggestions? Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
This worked! Thanks folks!!
"Tim879" wrote: try this formula. It assumes the date you want to convert (i.e. 6/14) is in cell A14. It will always return the Friday following the date you enter. If you enter a Friday, it will return the same date. =+A14+7-IF(WEEKDAY(A14,1)=6,1,IF(WEEKDAY(A14,1)=7,2,WEEKDA Y(A14,1)+2)) On Jun 20, 9:18 am, drew wrote: I am trying to convert a specific date to a Week Ending value. The week starts on Saturday and ends on Friday for this example. So.. any value from 6/14 through 6/20 would return a value of week ending 6-20-2008. Any suggestions? Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
On Fri, 20 Jun 2008 06:29:46 -0700 (PDT), vezerid wrote:
One way: for a date in A2, next Friday: =CEILING(A2+1,7)-1 HTH Kostis Vezerides That only works with the 1900 date system. With the 1904 date system, commonly used on Mac's, it will not give the correct answer. --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
On Fri, 20 Jun 2008 06:18:01 -0700, drew
wrote: I am trying to convert a specific date to a Week Ending value. The week starts on Saturday and ends on Friday for this example. So.. any value from 6/14 through 6/20 would return a value of week ending 6-20-2008. Any suggestions? Thanks! =A1+7-WEEKDAY(A1+1) --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Dates to Week Ending time period
You are right Ron, thanks for the pointer.
Kostis On Jun 20, 5:21 pm, Ron Rosenfeld wrote: On Fri, 20 Jun 2008 06:29:46 -0700 wrote: One way: for a date in A2, next Friday: =CEILING(A2+1,7)-1 HTH Kostis Vezerides That only works with the 1900 date system. With the 1904 date system, commonly used on Mac's, it will not give the correct answer. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering week ending dates | Excel Discussion (Misc queries) | |||
How to format cells to show dates as the week-ending date of that | Excel Worksheet Functions | |||
Counting dates greater than a certain time period | Excel Worksheet Functions | |||
Converting time period in hours | Excel Worksheet Functions | |||
Starting work period on a Saturday and ending on a friday | Excel Discussion (Misc queries) |