Feeds:
Posts
Comments

Posts Tagged ‘date entry format’

This problem has driven me to distraction with frustration ever since I upgraded to my (now not so new and already partially broken ¯\_(ツ)_/¯  ) new laptop.

I use spreadsheets a lot and for multiple purposes. I’m also not a super technology-type person.

Photo by Vivek Doshi on Unsplash

My issue boils down to learned behavior. You know, when you do something so much that it’s an automatic reflex.

Typing is a learned behavior once you really know how to type. You know what words and numbers you want and your fingers do it without you having to consciously focus on them.

On my day job, in every program we use including Excel, it’s the same date format: month day year. All day, every day, Monday to Friday. Month day year. Month day year. Month day year. An endless stream of typing month day year in the same format: 5/27/21

My previous laptop was the same: month day year.

I’m so used to it that it’s an actual burden to have to stop and think to type anything other than month day year. It’s the same typing format always and it automatically translates to show the date in any form you formatted it to, whether it’s the short numerical form (8/15/21), short date form (Aug 15/21), or long formal date form (August 15, 2021). You can even set it to show day month year or any other order after you type 8/15/21.

  • 5/27/21
  • 6/30/21
  • 3/18/21
  • 2/28/21
  • 8/15/21
  • 5/5/21
  • 5/8/21

    .

You get the idea. Consistency is golden. Your dates are always correct when you enter them consistently, in my case month day year. You don’t have to stop and question, or go back and verify anywhere, was that May 8 or August 5th.

     .

     .

Why oh why did Excel suddenly demand a new date format?

The issue and where the frustration lays is that when I reinstalled Microsoft Office on the new laptop, it flat out refused to use the ‘month day year’ date format.

I periodically tried repeatedly to reset the date format in Excel to take month day year. But no, it persisted in only accepting day month year. My dates kept coming up wrong and I ended up taking the more lengthy process of entering them as text instead of dates: ‘May 8/21 instead of 5/8/21.

I periodically tried researching how to fix it with no luck finding any answers.

Yeah, it sounds like a minor issue. But entering dates as text renders all formulas using those date boxes unusable.

    .

I like formulas. They make life easier.

For example, if I submit to a publisher who does not respond unless the story is accepted, but instead tells you to assume you’ve been rejected if you don’t hear from them in 75 days.

If I enter the date I submitted as May 8/21 text, I have to count 75 days on the calendar to find the date I should assume they rejected my story.

On the other hand, let’s say in the spreadsheet box K11 I entered the date I submitted my story properly (5/8/21), Excel now sees it as a readable date number. In box M11 where I want the assumed rejection date of 75 days after May 8th, I add the very simple formula =K11+75 and Excel automatically finds that rejection date (Jul 22/21) for me in the fraction of seconds it took me to type =K11+75 (in this case actually =<arrow over two boxes>+75, which is even faster).

With the ability to use formulas that use date boxes, you can also create formulas that will average how long a particular publisher you submit to frequently takes to respond, the longest time it took them to respond, or the same for all publishers’ responses.

    .

For me, not being able to use formulas on Excel boxes with dates is the equivalent to the dating dealbreaker. It’s just a big fat NO.

     .

The Solution to the Excel Date Entry Format

There is absolutely nowhere in Excel or any Microsoft Office program that allows you to change your date format to determine whether you should enter mm/dd/yy, dd/mm/yy, yy/mm/dd, or any other variation of 5/15/21.

The key date format is in your operating system. Windows, for example. That is where you need to fix it.

Microsoft Office pulls the date format it uses from your operating system.

Buggers.

I’m not familiar with Apple, so if you have a similar issue with a program I you can maybe try a similar fix, but for Windows here is where you need to fix it:

*What you see depends on what version of Windows you are running.

  1. Open your Control Panel (Settings).
  2. Click on Clock, Language, and Region (Time & Language).
  3. Click on Change date, time, or numbers formats  (Date, Time, & Regional formatting – scroll down to it).
  4. Under the Formats tab (scroll down to Related Settings), click on Additional settings (Additional, Date, Time & Regional Settings).
  5. Click on Time (Region: Change date, time, or number formats).

Make your changes here and click Apply and OK:

   .

You should see the little clock in your computer taskbar change to show your new date format if you changed it, for example, from day month year to month day year.

   .

   .

I’m still working on fixing the why my Microsoft Word documents are all such smaller print on my laptop screen now at 100% scale. My eyes are not going to get any younger!

Changing the Windows screen resolution settings just makes everything in every app and on the Windows desktop …

SUPER BIG!

Or small like Word. It also completely messes up some programs that require a specific screen size/resolution to work properly.

And yet, I’ve downloaded word templates that are normal sized on my screen.

That’s a problem for another day.

   .

Keep writing my friends.

Read Full Post »

%d bloggers like this: