Another short day formula formatting question?

Mitchell

New Member
Joined
Mar 20, 2007
Messages
37
Thank you everyone for the help in (Change date format layout from ddd.). This is second situation for the same planner worksheet that has 2 different type of formulas that are making me crazy! <g> I again have limited space for printing so abbreviating the workdays down again is the ideal solution.

- K2 is user input box for the starting date.
- B2 to H13 get filled, one full calendar week per row, Monday to Sunday.

The user-input date gets put into B2 that has this formula that starts the dates on the immediately-following Monday:
=IF(K2<>"",K2+7-WEEKDAY(K2+7-2),"")

And C2 to H13 get populated with my mundane, extremely pedestrian formula and its variations to increment by one day:
C2: =IF($B$2<>"",SUM($B$2+1),"")
D2: =IF($B$2<>"",SUM($B$2+2),"")
E2: =IF($B$2<>"",SUM($B$2+3),"")
(I know, crude, but I don't know a more elegant way and this does get the job done <g>.)

The challenge is that although I have font set to 6, it's still a bit too wide for my Micro planner and would like to change ddd to my custom short day format of:
M, T, W, Th, F, S, Sn.
The custom format for each cell of B2 to H13 is: ddd.mmm.dd.yyyy [i.e., recent help got me this formula for another part of the same planner, just to give an example: =TEXT(TODAY(),"yyyy-mm-dd")& " ." &CHOOSE(WEEKDAY(TODAY()),"Sn","M","T","W","Th","F","S")].

Please, how could one get the yyyy.mm.dd.ddd for the formulas below, then, to display as 2020-12-21.M, in lieu of 2020-12-21.Mon?:

-----------------------------------------------
=IF(K2<>"",K2+7-WEEKDAY(K2+7-2),"")
-----------------------------------------------
=IF($B$2<>"",SUM($B$2+1),"")
-----------------------------------------------

Thank you!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You can Replace TODAY() with your formula
Excel Formula:
=TEXT(IF(K2<>"",K2+7-WEEKDAY(K2+7-2),""),"yyyy-mm-dd")& " ." &CHOOSE(WEEKDAY(IF(K2<>"",K2+7-WEEKDAY(K2+7-2),"")),"Sn","M","T","W","Th","F","S")
Excel Formula:
=TEXT(IF($B$2<>"",SUM($B$2+1),""),"yyyy-mm-dd")& " ." &CHOOSE(WEEKDAY(IF($B$2<>"",SUM($B$2+1),"")),"Sn","M","T","W","Th","F","S")

Also If you don't have problem with 2-digit year, change yyyy to yy or totaly yy-m-d
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
SUM($B$2+1)
Just a general comment: SUM in this expression does nothing since there is just a single number in the parentheses. Therefore it can be written simply as
$B$2+1
 

Mitchell

New Member
Joined
Mar 20, 2007
Messages
37
Good Morning!

I'm sorry that I made a mistake when I gave one of the target formats above (I was working on 2 workbooks at the same time)! The end format date needed should all be in ddd.mm.dd.yyyy. My bad!

I've spent last hour and a half trying to work with the above kindly given code, but I'm still not getting how this all works so keep getting weird results on my attempts to change the order of the elements of the format. So have to throw in the towel again! <bg><sigh> I hope my explanation is better this time!

-----------------------
K2 is my date entry cell. Since I don't have a calendar button and it just takes a text date, I usually just leave it as today's date if I'm working in the current week.

A2 picks it up and changes the date to the previous Monday (seems better than the following Monday in actual practice, which is why I changed it).

So A2 is:
=IF($K$2<>"",K2-WEEKDAY(K2-2),"")

The rest of the cells are based on A2, and work along these lines:
=IF($B$2<>"",B2+1,"")

The cells are formatted with:
ddd.mm.dd.yyyy

but as stated above, I really would like to have my short day format instead of the 3-character ddd:
M, T, W, Th, F, Sa, S

so that instead of today's date, say, being display as
Wed.Jan.20.2021
it will appear as
W.Jan.20.2021

The planner page currently prints out wider than the normal page size so I've had to fold over the edge as the font is as small as can comfortably be read But putting in my preferred short date will fix that yet leave the rest of the date in easy, readable format (i.e., I prefer the 4-digit year here over 2-digit for ease of use).

Thank you for your help, guys, and patience <lol>! This planner that I've done up has allowed me to save a ton of money, and I've customized it to exactly what I need. Definitely a win-win. And it's been enormously helpful during quarantine as there's so much to track and do as I'm constantly looking and applying for work (I was laid off due to Covid, as I'm sure has happened to many folks around the globe and here, too, probably!). So this will finalize the last bit needed for my planner in terms of functionality.

Cheers and stay safe!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,853
Messages
5,627,263
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top