Help with Dates and Finding a Date in a range

[bboysen]

So I have a excel sheet that I send out to field crews for them to fill out and send back to me every Friday. What I am looking for is to get the system date and place it in a cell then find the next Friday and populate the dates for the week. This is a hard one to explain but any help would be great.

Thanks

 

[Lewiy]

As a formula, to get today’s date:
=TODAY()
To get the next Friday date:
=TODAY()+IF(6-(WEEKDAY(TODAY()))<0,6,6-(WEEKDAY(TODAY())))
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
But this will change every time the date changes, so I’m guessing you might want a macro solution. This is also possible, but you could copy and paste values for the above as a simple alternative if that is good enough?

 

[jasonb75]

Maybe I'm misreading this but I'm assuming that you would want friday of next week, not current week.

I could be wrong but this appears to simple to be the correct solution as it could give wrong dates depending on how far in advance the sheet is created.

System date entered in B1 using Ctrl ;

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 101px"><COL style="WIDTH: 105px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>System Date</TD><TD style="TEXT-ALIGN: right">30 Thu Dec 2010</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Next Friday</TD><TD style="TEXT-ALIGN: right">7 Fri Jan 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Dates for week</TD><TD style="TEXT-ALIGN: right">1 Sat Jan 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD style="TEXT-ALIGN: right">2 Sun Jan 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD style="TEXT-ALIGN: right">3 Mon Jan 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD style="TEXT-ALIGN: right">4 Tue Jan 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD style="TEXT-ALIGN: right">5 Wed Jan 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD style="TEXT-ALIGN: right">6 Thu Jan 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD style="TEXT-ALIGN: right">7 Fri Jan 2011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=CEILING(B1,7)+6</TD></TR><TR><TD>B3</TD><TD>=B4-1</TD></TR><TR><TD>B4</TD><TD>=B5-1</TD></TR><TR><TD>B5</TD><TD>=B6-1</TD></TR><TR><TD>B6</TD><TD>=B7-1</TD></TR><TR><TD>B7</TD><TD>=B8-1</TD></TR><TR><TD>B8</TD><TD>=B9-1</TD></TR><TR><TD>B9</TD><TD>=B2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>