Forum Discussion
Can I auto-fill MM/DD based on date entered in a different field?
Hi!
I have a form that auto-fills the individual dates for a week based on the week-ending date entered by the user into cell M8.
M8 is formatted - Date 3/14/2012
The code (script?) I'm using in the date fields (this one is to auto-fill the date for Sunday (with Saturday being the week ending date)) is:
IF(ISBLANK($M$8)," ",DAY($M8-6))
I now need to include the month along with the date in each of the auto-fill (MM/DD) fields. Is there a code to accomplish this or do I need to set up separate cells, one for MM and one for DD, for each date?
Thank you so much!!
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
7 Replies
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
