Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting date to a monday date
I need to take a date and convert it to a monday date.
I use the formula =today() in one cell (a1) and then in another cell(b1) I need it to read the monday date ie: 3/3. so if today is 3/6/08 (in a1) the it(b1) would read 3/3/08 .. when a1 is 3/11/08 then b1 would read 3/10/08 and so on. any help would be appreicated -- cjm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting date to a monday date
=A1-WEEKDAY(A1,2)+1
-- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "uvknights" wrote in message ... I need to take a date and convert it to a monday date. I use the formula =today() in one cell (a1) and then in another cell(b1) I need it to read the monday date ie: 3/3. so if today is 3/6/08 (in a1) the it(b1) would read 3/3/08 .. when a1 is 3/11/08 then b1 would read 3/10/08 and so on. any help would be appreicated -- cjm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting date to a monday date
One way:
Col A Col B Date =Date-Weekday(Date)+2 The WEEKDAY function calculates the day of the week of a given date where 1 = Sunday (as I have it now). First, subtract the day of the week from the original date. Then, since Monday is the second day, just add two to that date (the previous Saturday) to get the Monday date. Try it! HTH, Matthew Pfluger "uvknights" wrote: I need to take a date and convert it to a monday date. I use the formula =today() in one cell (a1) and then in another cell(b1) I need it to read the monday date ie: 3/3. so if today is 3/6/08 (in a1) the it(b1) would read 3/3/08 .. when a1 is 3/11/08 then b1 would read 3/10/08 and so on. any help would be appreicated -- cjm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting date to a monday date
I believe this will do what you want...
=A1-WEEKDAY(A1)+2 Rick "uvknights" wrote in message ... I need to take a date and convert it to a monday date. I use the formula =today() in one cell (a1) and then in another cell(b1) I need it to read the monday date ie: 3/3. so if today is 3/6/08 (in a1) the it(b1) would read 3/3/08 .. when a1 is 3/11/08 then b1 would read 3/10/08 and so on. any help would be appreicated -- cjm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting date to a monday date
Awesome ! All three worked perfectly. I'm just a beginner and your help was
greatly appreciated!!! -- cjm "uvknights" wrote: I need to take a date and convert it to a monday date. I use the formula =today() in one cell (a1) and then in another cell(b1) I need it to read the monday date ie: 3/3. so if today is 3/6/08 (in a1) the it(b1) would read 3/3/08 .. when a1 is 3/11/08 then b1 would read 3/10/08 and so on. any help would be appreicated -- cjm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date should always be Monday | Excel Discussion (Misc queries) | |||
Date calculation for Monday of one month to the Monday of the next | Excel Discussion (Misc queries) | |||
Finding the Monday date based on a different date in same week | Excel Worksheet Functions | |||
Date to a day (Monday, Tue, etc?) | Excel Discussion (Misc queries) | |||
Finding The Next Monday From a Date | Excel Programming |