×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

# Contact US

Log In

#### Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
To Your Posts
• Keyword Search
• One-Click Access To Your
Favorite Forums
• Automated Signatures
On Your Posts
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

# Excel 365 (2016) Working to auto-populate dates3

 Forum Search FAQs Links MVPs

## Excel 365 (2016) Working to auto-populate dates

(OP)
Hi there,
Please see attached the starting of a Temperature Chart showing the Average temps for each day of every week of the year (2024). It is a pain, in Column B to enter the dates manually. I tried with the "week" and "weekday" functions but I'm lost.

Not the RESOURCE link, this is for ease of accessing my resource, only.

I'm sure there has got to be a learning experience here for me... :) Do I have to enter these dates manually or is there an easier way?

Thanks in advance...
Laurie

### RE: Excel 365 (2016) Working to auto-populate dates

2
I've added three columns outside the table for potential use. The red date, beginning of first week, is the only value to enter. All other is calculated:
- end of week: [beginning of week] + 6
- beginnning of next week: [beginning of week] + 7
- week number: formula WEEKNUM([end of week], 1), the last argument (also default) indicates Sunday.

'Week XX' text is a result of custom format "Week "00 of week number.

However, there is one extra column comparing to your template.

combo

### RE: Excel 365 (2016) Working to auto-populate dates

For those who are using Microsoft 365 or Excel 2021 or greater...

1. Add a reference cell containing the Week 1 Start Date, in this instance, 12/31/2023. In my example, L1.

2. Using the LET() function...
=LET(a,(RIGHT(A2,2)-1)*7+$L$1,TEXT(a,"mm/dd")&" - "&TEXT(a+6,"mm/dd"))

3. Results

Skip,

Just traded in my OLD subtlety...
for a NUance!

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

### RE: Excel 365 (2016) Working to auto-populate dates

And here's a solution simply adding column C, where column B is the week Fome Date and column C is the week To Date. And I fixed the Divide By Zero error with IFERROR().

The Start Date for the year is in B2. Every other date is a simple prev date + formula.

Skip,

Just traded in my OLD subtlety...
for a NUance!

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

### RE: Excel 365 (2016) Working to auto-populate dates

(OP)
Sorry I'm late in responding, things are busy and so am I. Late Night is when I get to focus.

combo, I appreciate your example, and will take a look to see what you are specifically talking about and hopefully, between your example and Skip's... I will get this. It seems I will.

Skip, I really appreciate your example too... I knew about the "div/0" error thingy, just had not looked up the example yet so thanks for that refresher. As for the others, I'll dig in later this evening.

I truly thank you for your help, both of you and STARS ABOUND! You always save my bacon and educate me so much. I truly value and absolutely appreicate you both for always coming through :)

Laurie

### RE: Excel 365 (2016) Working to auto-populate dates

(OP)
Gentlemen...

FANTASTIC! I used a little bit from each of your suggestions and have the perfect worksheet. I'm overflowing with JOY!

Skip, the first suggestion you provided for us 365/2016 users, well I've got to play with that to grasp it and that will come tomorrow. In an effort to learn more :)

THANKS!!!!!!!!!!!!!
Love learning (retaining is another story) but learning is joyful!
:)

### RE: Excel 365 (2016) Working to auto-populate dates

Laurie, to clarify

https://support.microsoft.com/en-us/office/what-s-...

This part is good news...

"Microsoft 365 for the web is a free version of Microsoft 365 that you can use in a web browser. All you need is to sign up for a Microsoft account with a new or existing email address. Use Word, Excel, PowerPoint and more for free on the web."

I use a free version of Excel on my iPad. It has nearly all the features I currently use at home. If I were still working in industry, I'd want access to all the bells & whistles.

Skip,

Just traded in my OLD subtlety...
for a NUance!

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

### RE: Excel 365 (2016) Working to auto-populate dates

This little task is just asking for a few lines of VBA code...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

### RE: Excel 365 (2016) Working to auto-populate dates

(OP)
Skip,
I have a subscription to 365, I just hate using Excel on handheld devices, too small. And I don't do any productive work on my ipad, cuz well... it annoys me. I'm just older school <wink> I don't like having anything "in the clouds" however I do store my OneNote stuff out there so I can use it cross-platform (Android phone, iPad and Windows 11) I do 99.9999999999999%, well to be honest, 100% of my excel on my PC. I open it on a device, get shudders down my spine and give up. :)

Andy,
Thanks... I do appreciate you keeping up with my inquiries, but I got this ... this time... the previous recommendations did work. HOWEVER, I'm sort of game, if you want to... no promises that I'll get it or remember how to use it.

I appreciate all three of you, always my champions!!
Laurie

### RE: Excel 365 (2016) Working to auto-populate dates

OK, just for 'kicks and giggles'...

#### CODE

Sub ladyck3_Dates()
Dim i As Integer
Dim dat As Date

Columns("A:C").NumberFormat = "@"

dat = DateSerial(Year(Date), 1, 1)
Cells(1, 1).Value = Year(dat)

Do Until Weekday(dat) = vbSunday
dat = DateAdd("d", -1, dat)
Loop

i = 1
Do Until Year(dat) > Year(Date)
Cells(i + 1, 1).Value = "Week " & Format(i, "00")
Cells(i + 1, 2).Value = Format(dat, "mm/dd")
Cells(i + 1, 3).Value = Format(DateAdd("d", 6, dat), "mm/dd")
dat = DateAdd("d", 7, dat)
i = i + 1
Loop

End Sub 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

### RE: Excel 365 (2016) Working to auto-populate dates

(OP)
Thanks Andy,

Will add it to my notes and will have to research (my notes) to figure out how to use it :)

I appreciate your time, and continued efforts.

### RE: Excel 365 (2016) Working to auto-populate dates

"figure out how to use it"
In new, empty Excel file, hit Alt-F11 (VBA editor), add a Module, paste this code, place your cursor at the top and hit F5 (Run). Observe the magic.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

### RE: Excel 365 (2016) Working to auto-populate dates

Laurie, what I found helpful is to use the Macro Recorder to do just one thing. Then STOP the recorder and hit Alt-F11 (VBA editor), and observe the code that the recorder produced for the one action you performed.

The next step is to customize the recorded code, because the recorded code is not necessarily the best code to use in a macro. The VBA users here can help you with this step. forum707: VBA Visual Basic for Applications (Microsoft).

Then test the modified code.

You might rename this procedure as a stand-alone or incorporate some or all the code in some other procedure.

Skip,

Just traded in my OLD subtlety...
for a NUance!

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

### RE: Excel 365 (2016) Working to auto-populate dates

(OP)
Thank you Andy and Skip....

I'll, honestly, review this ... I have some "life things" happening and can't jump into it right at the moment. I hope you understand.
I do... appreciate this... :)

Laurie

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

### Reply To This Thread

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.