Date format

[Omi]

Hi,

I want to write a formula, such that everytime Column B is
a Monday the Date in Column C be copied over from the
previous Friday . (Sep 8 Mon Sep 5)

A B C
05-Sep-03 Fri 29.Aug
08-Sep-03 Mon 30.Aug
09-Sep-03 Tues 31.Aug
10-Sep-03 Wed 01.Sep
11-Sep-03 Thurs 02.Sep
12-Sep-03 Fri 03.Sep
15-Sep-03 Mon 04.Sep
16-Sep-03 Tues 05.Sep

Thx
Omer

 

[Ron Rosenfeld]

Hi,

I want to write a formula, such that everytime Column B is
a Monday the Date in Column C be copied over from the
previous Friday . (Sep 8 Mon Sep 5)

A B C
05-Sep-03 Fri 29.Aug
08-Sep-03 Mon 30.Aug
09-Sep-03 Tues 31.Aug
10-Sep-03 Wed 01.Sep
11-Sep-03 Thurs 02.Sep
12-Sep-03 Fri 03.Sep
15-Sep-03 Mon 04.Sep
16-Sep-03 Tues 05.Sep

Thx
Omer

Your description of your problem leaves out some information.

1. What do you want to see in column C if Column B does NOT say Mon (or
Monday)?

2. In your data, the day in column B corresponds to the date in Column A. Is
this always the case?

3. Is the contents of column B a date (formatted to show a day of the week) or
is it a string?

Assuming the contents of column B always represents the day of the week of the
date in Column A, then:

=IF(WEEKDAY(A2)=2,A2-3,"")

might do what you want. It will put nothing in column C unless the date in
column A is a Monday.






--ron