[tussinelde]

I have a timecard spreadsheet that I need help with.

One of the cells on the card represents "week starting", cell N4, (for instance, this week that cell is Jan. 8, 2012). There is another cell that represents "week ending", cell N5, and is based off of the other cell. This is what is the 2nd cell has:

Code:

Code

IF($N$4=0,"",$N$4+6)

Now, how can I make it do this:

IF: N4's date is either Jan. 1, 2012 or a multiple of 7 days after that date: if today's date is Jan 1, Jan 8, Jan 15 etc...
THEN: change that cell to today's date

I want it to automatically update the week starting and week ending as the document is opened/used so I don't have to manually change the date.

[Sydneyfonzi]

I suppose you could use the text() function to check if it's a Monday?

I'm struggling to create a mental image of what these cells are doing however.
Though I'm sure someone else will understand better than I have, is there a possibility for a screeny or example file?

[Heliosprime]

[offtopic]I just about passed Excel doing my ECDL... never touched it since, thank christ.

[obHQNsY2]

This will do what you want but there may be a better way if I could see the spreadsheet. This is pretty efficient though and will auto update.

Code:

Code

=IF(MOD(TODAY()-N4,7)=0,TODAY(),"Whatever you want this to be if it isn't a 7 day multiple")

[mXr8icOB]

This will do what you want but there may be a better way if I could see the spreadsheet. This is pretty efficient though and will auto update.

Code:

Code

=IF(MOD(TODAY()-N4,7)=0,TODAY(),"Whatever you want this to be if it isn't a 7 day multiple")

I think you need to wrap it in another IF to check if the date is 1st January 2012 as well...?

[Rjvpicux]

Probably, this just does 7 day cycles from whatever first date is referenced. I don't know what he's trying to do with the rest of it.

[flnastyax]

I'm not sure if Excel supports this syntax, but you need something like:

IF(MOD(TODAY()-N4,7)=0 OR TODAY() = '1 jan 2012',TODAY()

[Viyzarei]

I'm not sure if Excel supports this syntax, but you need something like:

IF(MOD(TODAY()-N4,7)=0 OR TODAY() = '1 jan 2012',TODAY()

This should encapsulate it (I wrote it in the reply rather than in a spreadsheet so there might not be enough brackets) if it's required.
Code:

Code

=IF(OR(DATE(2012,1,1)=TODAY(),MOD(TODAY()-N4,7)=0),TODAY(),"Whatever you want this to be if it isn't a 7 day multiple")