How to convert the given date-time format into week start date with day of the week?

[#1]

Friends,

I want to convert the DateTime format into week start date with it's day?. For example:

4/15/2013  9:36:02 AM This need to be converted into (-W-day). Help me to solve this.

If I use weekStart(''4/15/2013  9:36:02 AM') it will return the format like (4/15/2013).

I Need something like 2013-W48-('week start day') OR (W48-Mon) and so on..

Thanks,

[jagan]

Hi,

Try like this

=Date(Date#('4/15/2013 9:36:02 AM', 'M/D/YYYY h:mm:ss TT'), 'YYYY') & '-W' &  Week(Date#('4/15/2013 9:36:02 AM', 'M/D/YYYY h:mm:ss TT')) & '-' & WeekDay(WeekStart(Date#('4/15/2013 9:36:02 AM', 'M/D/YYYY h:mm:ss TT')))

Regards,

Jagan.

[tresesco · Accepted solution]

May be like this:

=Year(WeekStart(today()))&'- W'&Week(WeekStart(today()))&'-'&Weekday(WeekStart(today()))

Replace today() with your date.

[#4]

Hi Tresesco,

Thanks for your replay. And it's working correctly. Another one doubt, Is there any possible to show the actual day instead of the start day?.

Regards,

Thiru

[CELAMBARASAN]

Try with this

=Replace(WeekName(Today()), '/', '-W') & '-' & WeekDay(WeekStart(Today()))

In the place of Today() you can use Date#('4/15/2013 9:36:02 AM', 'M/D/YYYY h:mm:ss TT').

[tresesco]

Just remove the weekstart() function, it would give you actual day.

[#7]

Hi Tresesco,

It's working correctly.

Thanks,

[#8]

Hi Celambarasan,

Thanks,