Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating Problem
I need a UDF that, given a date, returns the date of the Monday of that week.
For example, if the input was a value between 6/20/2005 and 6/26/2005, the function would return 6/20/2005. If the input was 6/27/2005 the UDF should return 6/27/2005. All I can think of is a massive VLOOKUP table. There has got to be a cleaner way. Thank you in advance -- Gary's Student |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating Problem
Function WeeksMonday(Dt As Date) As Date
WeeksMonday = Dt - Weekday(Dt, vbMonday) + 1 End Function HTH. Best wishes Harald "Gary's Student" skrev i melding ... I need a UDF that, given a date, returns the date of the Monday of that week. For example, if the input was a value between 6/20/2005 and 6/26/2005, the function would return 6/20/2005. If the input was 6/27/2005 the UDF should return 6/27/2005. All I can think of is a massive VLOOKUP table. There has got to be a cleaner way. Thank you in advance -- Gary's Student |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating Problem
A worksheet function:
=A1-(WEEKDAY(A1)-2)-IF((WEEKDAY(A1)-2)<0,7,0) Mangesh "Gary's Student" wrote in message ... I need a UDF that, given a date, returns the date of the Monday of that week. For example, if the input was a value between 6/20/2005 and 6/26/2005, the function would return 6/20/2005. If the input was 6/27/2005 the UDF should return 6/27/2005. All I can think of is a massive VLOOKUP table. There has got to be a cleaner way. Thank you in advance -- Gary's Student |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating Problem
Thank you both
-- Gary's Student "Mangesh Yadav" wrote: A worksheet function: =A1-(WEEKDAY(A1)-2)-IF((WEEKDAY(A1)-2)<0,7,0) Mangesh "Gary's Student" wrote in message ... I need a UDF that, given a date, returns the date of the Monday of that week. For example, if the input was a value between 6/20/2005 and 6/26/2005, the function would return 6/20/2005. If the input was 6/27/2005 the UDF should return 6/27/2005. All I can think of is a massive VLOOKUP table. There has got to be a cleaner way. Thank you in advance -- Gary's Student |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating Problem
On Tue, 21 Jun 2005 03:29:03 -0700, "Gary's Student"
wrote: I need a UDF that, given a date, returns the date of the Monday of that week. For example, if the input was a value between 6/20/2005 and 6/26/2005, the function would return 6/20/2005. If the input was 6/27/2005 the UDF should return 6/27/2005. All I can think of is a massive VLOOKUP table. There has got to be a cleaner way. Thank you in advance What is the first day of your week? If the first day of your week is Sunday, then: =A1+2-WEEKDAY(A1) If the first day of your week is Monday, then: =A1+1-WEEKDAY(A1,2) In VB: Function FirstMon(dt As Date) As Date FirstMon = dt + 2 - Weekday(dt, vbSunday) End Function or Function FirstMon(dt As Date) As Date FirstMon = dt + 1 - Weekday(dt, vbMonday) End Function --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet Dating Issue | Excel Discussion (Misc queries) | |||
Dating a file. | Excel Discussion (Misc queries) | |||
How do I change from american dating to uk dating? | Excel Worksheet Functions | |||
How to update dating? | New Users to Excel | |||
dating problems! | Excel Worksheet Functions |