Microsoft Office Forums Auto populate weekday based on given cell/date

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-18-2019, 11:20 AM
kelwea kelwea is offline Auto populate weekday based on given cell/date Mac OS X Auto populate weekday based on given cell/date Office 2016
Novice
Auto populate weekday based on given cell/date
 
Join Date: Jan 2017
Posts: 5
kelwea is on a distinguished road
Default Auto populate weekday based on given cell/date


In the attached spreadsheet, I want to auto populate row 4 (days of the week) based on the current date in C1 [=today()]. Is this possible? Thank you.
Attached Files
File Type: xlsx Platoon T2T for 3-6 AMDTD.xlsx (43.1 KB, 4 views)
Reply With Quote
  #2  
Old 05-18-2019, 07:20 PM
Alansidman's Avatar
Alansidman Alansidman is offline Auto populate weekday based on given cell/date Windows 10 Auto populate weekday based on given cell/date Office 2019
Advanced Beginner
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 30
Alansidman is on a distinguished road
Default

In C4 =WEEKDAY(C1) Custom Format DDD. In C5 =C4 +1. Copy C5 to the end of Row 4.
Reply With Quote
  #3  
Old 05-19-2019, 11:15 AM
kelwea kelwea is offline Auto populate weekday based on given cell/date Mac OS X Auto populate weekday based on given cell/date Office 2016
Novice
Auto populate weekday based on given cell/date
 
Join Date: Jan 2017
Posts: 5
kelwea is on a distinguished road
Default @alansidman

The issue I am seeing with that formula is the referenced date in C1 starts in the middle of the month. So I have to start at that day, subtract to the left and add to the right. Then the days become inaccurate moving forward.

So, in the updated spreadsheet that I've attached, I made some changes. I have created tabs for each quarter as well as a 'Reference' tab where I put the first day of the year (Tuesday, January 1, 2019). I am using that reference cell with the formula =TEXT(WEEKDAY(Reference!$A1),"DDD"), which seems I will have to add (+1, +2, etc.) manually for each cell in row 4, unless I can find a way to auto-populate.

What I would like to do is find formulas that will automatically (and accurately) populate rows 3 and 4 (in that format) for each month. And having it account for leap years would be a huge bonus!!
Attached Files
File Type: xlsx Platoon T2T for 3-6 AMDTD.xlsx (135.2 KB, 3 views)
Reply With Quote
  #4  
Old 05-19-2019, 01:29 PM
Alansidman's Avatar
Alansidman Alansidman is offline Auto populate weekday based on given cell/date Windows 10 Auto populate weekday based on given cell/date Office 2019
Advanced Beginner
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 30
Alansidman is on a distinguished road
Default

I'm not understanding what you hope to achieve. Please explain in simple business terms and not in Excel terms what you are trying to achieve. Once we understand your specific needs, we can address a viable solution.
Reply With Quote
  #5  
Old 05-19-2019, 03:28 PM
p45cal p45cal is offline Auto populate weekday based on given cell/date Windows 10 Auto populate weekday based on given cell/date Office 2016
Expert
 
Join Date: Apr 2014
Posts: 281
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Is the attached what you wanted?
As far as leap years is concerned, all I've done is arrange for the bold vertical line between Feb and March to appear at the right place. [This was more convoluted than I expected because it turns out the you can't conditionally format thick borders! So I made borders of two columns thick manually and added conditional formatting to make the one which isn't the 1st of the month thin!]
Attached Files
File Type: xlsx msofficeforums42538Platoon T2T for 3-6 AMDTD-1.xlsx (136.4 KB, 5 views)
Reply With Quote
  #6  
Old 05-19-2019, 07:55 PM
kelwea kelwea is offline Auto populate weekday based on given cell/date Mac OS X Auto populate weekday based on given cell/date Office 2016
Novice
Auto populate weekday based on given cell/date
 
Join Date: Jan 2017
Posts: 5
kelwea is on a distinguished road
Default

Oh man! I believe it is what I was looking for p45cal! And it looks so much easier than I was making it. Did you just use cell formatting to get the desired output in the cell? And why did you have to use EDATE in Apr - Dec and not in the first tab? I'm trying to go through cell by cell to see just how you did it. I'm trying to learn these formulas better so hopefully one day soon I can help people like you guys are.

Also, thank you Alansidman for your help. I greatly appreciate it.
Reply With Quote
  #7  
Old 05-20-2019, 03:26 AM
p45cal p45cal is offline Auto populate weekday based on given cell/date Windows 10 Auto populate weekday based on given cell/date Office 2016
Expert
 
Join Date: Apr 2014
Posts: 281
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by kelwea View Post
Did you just use cell formatting to get the desired output in the cell?
Yes, all the cells concerned contain full-blown Excel dates
Quote:
Originally Posted by kelwea View Post
And why did you have to use EDATE in Apr - Dec and not in the first tab?
The EDATE formula returns a date n months forwards or backwards and takes account of leap years and the different numbers of days in the months. It's not needed for the first tab since the date (1st Jan) IS the date on the reference sheet.


ps. I think I missed adding conditional formattting to the March-April boundary too.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to auto-populate data based on cell value ag2686 Excel 2 06-13-2018 09:19 PM
is sit possible to auto populate a cell based on dates wheddingsjr Excel 6 11-27-2017 11:21 AM
Auto populate weekday based on given cell/date Auto populate cell with today's date Phil H Excel Programming 7 10-18-2017 09:41 AM
Auto populate cell based on certain situations YW3 Excel 10 05-26-2017 02:02 AM
Auto-populate an MS Word table cell with text from a diff cell? dreamrthts Word Tables 0 03-20-2009 01:49 PM


All times are GMT -7. The time now is 02:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft