Date Formula

**Pool Dave** · 01-03-2012, 05:16 PM

On the attached sheet is a part time shift pattern. I want to type in the column A just the day and need a formula in coumn B to generate the correct date based on the dates in sequence above - is this possible?

Thanks in advance.Date Query.xls

**teylyn** · 01-03-2012, 06:41 PM

Hello,

one possibility: in B3

=B1+MATCH(A2,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0)-MATCH(A1,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0)+7

copy to B7 and B10. The formula will look at the three letter day code in column A of the previous row and the date in column B of the previous row, so it will only work if there is a valid three letter code and date in column A and B.

cheers,

**Pool Dave** · 01-04-2012, 08:56 AM

Hi Teylyn

Sorry I should have been more specific!

I need to keep the existing blank rows blank, if that's possible. Also I wanted to take the folmula and run it down the column so I don't have to paste it into specific cells. This makes it very easy to add to or update.

What do you recon?

Thanks

Dave

**scottylad2** · 01-04-2012, 02:25 PM

Perhaps it would help if you could clarify the rationale to the shift pattern? is there a specific number of Fridays per month or is it the 1st and 3rd Sat? I've been looking at it for a while and it doesn't seem to have any fixed pattern

**Pool Dave** · 01-04-2012, 02:41 PM

I'm afraid it doesn't - apart from the fact that I work every Friday. There is not pattern to a Saturday or Sunday shift as such, sometimes it's just Friday on it's own.