Ask Your Question
0

How do I change cell data to a date one week in the future once that cell is equal to today's current date? [closed]

asked 2014-09-13 09:03:49 +0100

UTmike gravatar image

updated 2014-09-13 11:46:27 +0100

bencomp gravatar image

How do I change cell data to a date one week in the future once that cell is equal to today's current date?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-03 18:47:32.872455

1 Answer

Sort by » oldest newest most voted
0

answered 2014-09-13 14:14:39 +0100

Lupp gravatar image

updated 2014-09-13 15:30:44 +0100

Your data you will have to update yourself.

The calendar date shown by a cell can be updated by a formula. Assume "Monday, 2014-01-06" was the original date you started with. Then =VALUE("2014-01-06")+QUOTIENT(TODAY()+6-VALUE("2014-01-06");7)*7 will calculate the DayNumber (as calendaric dates are treted internally by spreadsheet software) of the actual day, if a monday, or the monday coming otherwise. To get it displayed in a date format you have to set it for the cell containing the formula.

This can easily be adapted to needs changed in detail. Best you prepare three cells for conating (e.g.):

C1: Starting date, say 2014-01-06 (which was a monday) (As a recognised date, not as a text!)

C2: Days offset (switching in advance)

C3: Weeks offset (switching in advance)

Any cell: =C1+QUOTIENT(TODAY()+6+C2+C3*7-C1;7)*7 and set to the proper numerical format for your dates

Editing: Sorry, both the formula above lacked a "+6" needed for the "minimum advance" . It is inserted now.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-09-13 09:03:49 +0100

Seen: 261 times

Last updated: Sep 13 '14