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

[ladyck3]

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

 

[combo]

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

 

[SkipVought]

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([highlight #FCAF3E]a[/highlight],[highlight #F57900](RIGHT(A2,2)-1)*7+$L$1[/highlight],TEXT([highlight #FCAF3E]a[/highlight],"mm/dd")&" - "&TEXT([highlight #FCAF3E]a[/highlight]+6,"mm/dd"))

3. Results


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[ladyck3]

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

 

[ladyck3]

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!

 

[SkipVought]

Laurie, to clarify

https://support.microsoft.com/en-us...ice-2021-ed447ebf-6060-46f9-9e90-a239bd27eb96

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Andrzejek]

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

---- Andy

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

 

[ladyck3]

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

 

[Andrzejek]

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

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

 

[ladyck3]

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.

 

[Andrzejek]

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

 

[SkipVought]

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.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[ladyck3]

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