working with dates

[mort1703]

Hi

I am working on a problem whereby I have the following columns

A1 - a frequency of MON-SUN
B2 - a date ie 02/06/2015

C1 - needs to contain the date based on A1

IE.

A1 B1 C1
FRI 02/06/2015 NEED TO RETURN 29/05/2015
THU 02/06/2015 NEED TO RETURN 28/05/2015

can anyone help?

 

[RickXL]

Hi,

Try this:

=B1-IF(A1="Mon",0,IF(A1="Tue",1,IF(A1="Wed",2,IF(A1="Thu",3,IF(A1="Fri",4,IF(A1="Sat",5,6))))))

 

[RickXL]

Hi,

Or better still, try this one.

=B1-IF(A1="Mon",8,IF(A1="Tue",7,IF(A1="Wed",6,IF(A1="Thu",5,IF(A1="Fri",4,IF(A1="Sat",3,2))))))

Thursday came after Friday in your list and I am easily confused

 

[mfexcel]

Are you trying to get the date of the previous week? In your example, what if
MON 02/06/2015

do you want to return 01/06/2015 (previous Monday), or 26/5/2015 (Monday of previous week)?

Hi

I am working on a problem whereby I have the following columns

A1 - a frequency of MON-SUN
B2 - a date ie 02/06/2015

C1 - needs to contain the date based on A1

IE.

A1 B1 C1
FRI 02/06/2015 NEED TO RETURN 29/05/2015
THU 02/06/2015 NEED TO RETURN 28/05/2015

can anyone help?