Working out the next Saturday date.

[Taslin]

Hey people!

I have a small problem in Excel. In column A I have normal day to day dates but in column B I want to display the next Saturday that appears on or after the date in column A. For example starting with 25/01/03 (Saturday)

Col A Col B
-------- --------
25/01/03 25/01/03
26/01/03 01/02/03
27/01/03 01/02/03
28/01/03 01/02/03

and so on and so ...

Thanks in advance for any help or tips!

 

[SgtJarrow]

While not an Excel expert, I solved this as such:

In column A, I placed some random dates.
In column B, I placed =Weekday(A#) ' # being the number of the row
In column C, I placed =IF(B#<>7,A#+7-B#,A#)

The I hid column B

I got the desired results every time...

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[TonyJollans]

Hi Taslin,

If your date is in A1, then in B1 put =FLOOR(A1+6,7) and format the cell as a date. Just copy the formula down column B as far as your dates in column A go.

Enjoy,
Tony

 

[Taslin]

Cheers Robert! Works a treat!

Taslin

 

[TonyJollans]

Perhaps I should explain. Days are held as numbers such that Saturday dates are divisible by 7. FLOOR rounds down to the nearest multiple of its second argument (in this case 7) thus returning the date number of the previous Saturday. Finding the Saturday on or before 6 days after a date is the same as finding the next one after a date.

As I write this I realise that =CEILING(A1) is much better. See Help if you can't guess from my description of FLOOr what this might do.

Enjoy,
Tony

 

[Wray69]

Good job Tony, I came up with something a bit longer but it worked... I always forget about floor and ceiling...
=(7-TEXT(WEEKDAY(A1),0)*1)+A1

Wray