#1
|
|||
|
|||
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.
|
#2
|
||||
|
||||
In C4 =WEEKDAY(C1) Custom Format DDD. In C5 =C4 +1. Copy C5 to the end of Row 4.
|
#3
|
|||
|
|||
@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!! |
#4
|
||||
|
||||
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.
|
#5
|
||||
|
||||
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!] |
#6
|
|||
|
|||
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. |
#7
|
||||
|
||||
Quote:
Quote:
ps. I think I missed adding conditional formattting to the March-April boundary too. |
|
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 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 |