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

Function WeeksMonday(Dt As Date) As Date
WeeksMonday = Dt - Weekday(Dt, vbMonday) + 1
End Function

HTH. Best wishes Harald

"Gary's Student" <[email protected]> skrev i melding
news:[email protected]...
> 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

A worksheet function:

=A1-(WEEKDAY(A1)-2)-IF((WEEKDAY(A1)-2)<0,7,0)

Mangesh



"Gary's Student" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

On Tue, 21 Jun 2005 03:29:03 -0700, "Gary's Student"
<[email protected]> 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