Auto Populate a text box with a date

amy

I have a spreadsheet where employees enter their work hours for the week and I am using a user form to collect the data and enter it to the spreadsheet. On the user form there is text box for the week ending date (in our case every Friday of the week). I need this text box to be automatically populated with the date of coming Friday of the week.

Any help will be greatly appreciated.

Nokoy

This formula will give you the upcoming Friday's date (format the cell as a date)

=IF(WEEKDAY(TODAY())=7,TODAY()+6,TODAY()+6-WEEKDAY(TODAY()))

Additionally, to get the value in the text box, select the text box, go into the formula bar, and type =A8 (replace A8 with the cell)

earlyd,

Thanks for the help. The text box is in the user form, if I undestand it  correct your suggestion is for text box on the sheet.

Thanks,

Nokoy

Hi,

try this code inserted into the userform_Activate event. Format the date as appropriate.Code:
Private Sub UserForm_Activate()
Dim Fri As DateFri = (Int(Date / 7) * 7) + 6

TextBox1.Text = Format(Fri, "dd-mmm-yyyy")
End Sub
HTH

Alan

Your form's text box can still refer to a cell in a sheet.

Thanks for the great help. I appreciate it very much.

Nokoy