Forum Discussion
Can I auto-fill MM/DD based on date entered in a different field?
Sure. I have put examples and explanatory text and Excel notes (in cells with a red marker in the upper right) into the attached workbook.
Absolute addressing (using the $ in front of the column letter and/or row number) tells Excel that if this cell is copied, leave this portion of the address as-is. Relative addressing (without the $) tells Excel that if this cell is copied, adjust the column letter and/or row number as seems to be appropriate; e.g., if the original formula referenced a cell two columns to that cell's left, Excel will change the pasted formula to refer to the cell two columns to this cell's left.
I'm guessing that your data is structured something like the worksheets in the attached workbook. With the upper formula from my post (I'll refer to it as formula 1), copying cell N8 to other rows can produce undesirable results, depending on what is in column M. Formula 2, on the second worksheet, behaves better, as shown.
Bonus format code: The code dddd displays the day of the week fully spelled out.
Bonus formula: Your spreadsheet might have adjacent dates in adjacent columns, as shown on the Formula2 worksheet. Rather that subtracting a different number of days from the Week Ending date in each of those columns, I put a formula in cell O8 that examines what column it is stored in (the "COLUMN()" part) and what column the Week Ending date is stored in (the "COLUMN($M8)" part) to calculate the date. That way, O8 can be copied to the right (as I have done, and back into cell N8 if you wish) without change. It can also be copied to other rows. If you don't understand that formula, feel free to ignore it.
Edit: clarified absolute addressing
The formula you are using generates just a one- or two-digit number in that cell. Unless you need such a number value there, it would be more useful to have the formula calculate instead the corresponding date value, and then use cell formatting to display just the day number, or the month and day (or various other date-related info) however you wish.
If you posted your formula correctly, the modified formula would simply be:
=IF(ISBLANK($M$8)," ",$M8-6)But that uses absolute referencing for the row number (the "$8" part), which does not lend itself to being copied to other rows, so I suspect you want instead:
=IF(ISBLANK($M8)," ",$M8-6)
As for the cell formatting, you can select among the built-in Date formats that show the month and day number. Or if you have specific requirements, such as displaying leading zeros for both numbers (03/08), you can create the custom format mm/dd:
Or try, e.g., the custom format mm/dd (ddd) (including the space), which produces 03/08 (Thu). Or if you still wanted just the day number, the custom format code could be d (no leading zeros) or dd (leading zeros) or dddd (the weekday fully-spelled), etc.
This worked wonderfully, Snowman55, thank you so much!! 🙂 (I'm sure you knew it would, lol) 😉
You mentioned near the end of your reply about using mm/dd (ddd) if I wanted to include the day of the week. I do have to include it but in a separate cell directly above the date cell.
I put your revised formula in one of the "day of the week" cells and formatted it as ddd (without parenthesis) and it worked. Again, thank you SO much!!
If you have the time I'd love to ask you a question, or more, about this:
"But that uses absolute referencing for the row number (the "$8" part), which does not lend itself to being copied to other rows"
I've tried phrasing my questions a couple times but can't quite figure out how to ask them because I think I still don't have enough grasp of what you're saying. When you have time, I know everyone's time is so valuable, would you mind explaining to me what you mean, why it won't work to copy it to other rows or what won't work?
Thank you again so much for your help *and* for educating me!
Diane
Sure. I have put examples and explanatory text and Excel notes (in cells with a red marker in the upper right) into the attached workbook.
Absolute addressing (using the $ in front of the column letter and/or row number) tells Excel that if this cell is copied, leave this portion of the address as-is. Relative addressing (without the $) tells Excel that if this cell is copied, adjust the column letter and/or row number as seems to be appropriate; e.g., if the original formula referenced a cell two columns to that cell's left, Excel will change the pasted formula to refer to the cell two columns to this cell's left.
I'm guessing that your data is structured something like the worksheets in the attached workbook. With the upper formula from my post (I'll refer to it as formula 1), copying cell N8 to other rows can produce undesirable results, depending on what is in column M. Formula 2, on the second worksheet, behaves better, as shown.
Bonus format code: The code dddd displays the day of the week fully spelled out.
Bonus formula: Your spreadsheet might have adjacent dates in adjacent columns, as shown on the Formula2 worksheet. Rather that subtracting a different number of days from the Week Ending date in each of those columns, I put a formula in cell O8 that examines what column it is stored in (the "COLUMN()" part) and what column the Week Ending date is stored in (the "COLUMN($M8)" part) to calculate the date. That way, O8 can be copied to the right (as I have done, and back into cell N8 if you wish) without change. It can also be copied to other rows. If you don't understand that formula, feel free to ignore it.
Edit: clarified absolute addressing
Hi!
I hope that your days have been going well! 🙂
I'm wondering if I can talk you into one more "bonus"? 😉
Is there a way to have the day display as MO rather than Mon, and so on (two upper-case characters instead of the 3-character option)?
Thank you so much!!
Diane
DianeDennis There is no two-character day-of-week format, nor a format that converts text to upper case, so you'll need to use a formula. The TEXT function builds the same text that the specified formatting would display, the LEFT function extracts characters at the beginning of text, and the UPPER function converts text to upper case. Working with a date value in cell N8:
=UPPER( LEFT(TEXT(N8,"ddd"),2) )
Outstanding, again, thank you so much! 🙂
I'm actually understanding it, getting my head wrapped around it! Great job with explaining it to me. 🙂
I also think that I am grasping what you did with O8.
I'm often in awe over what the members here are able to accomplish and you've got me there again today.
Thank you for these successes today and your kindness, I truly needed it all. 🙂
Diane