Resolving Date Issues in Excel

Quite often you will have a formatting issue in excel either it does not understand the format or you have extra data in a column you dont need. Before it will understand what you want.  ( A side note I do not remember it being this difficult on windows, but on Mac this is the solution I found).

 

In my case this is the data : Apr 6 2018 12:06:21:000AM its simply a date plus a time but excel does not understand it yet. In this example I just want the date formatted correctly and I will get rid of the time.

  1. Separate out the data into individual columns

  2. Hi-light the column and choose 'Text to Columns'

  3. Choose delimited

  4. Space delimited

  5. Finish


Now they are separated into the 4 columns, we can now delete the time in the last column.

We now need to combine the data back into 1 column - we can do this by using a formula like this : =M2&"/ "&N2&"/ "&O2 this now gives us Apr/ 6/ 2018 but excel does not understand its a date, even if you select format etc.

First we need to copy the values using paste special  - choosing 'values' only

Now what we can do is use text to columns again but into a single column to format it into date. to achieve this follow this guide : https://theexceltrainer.co.uk/converting-date-format-using-text-to-columns/

Now we are free to change the date format using the format option in excel.

 

 

Comments

Post a Comment

Popular posts from this blog

Suzuki Swift Sport 2011 - 2016 Jacking points

Image
So with any car the first thing I like to find out is where are the jacking points is there anything special I need todo to get it in the air. Luckily the Swift is super easy, see below for a diagram from the users manual Option 1 – Centre Jacking points There are two ways the Front centre jacking point / Rear center jacking point. Option 2 – Sill Jacking points Sill jacking points
Read more

K13 Nissan Micra - 2010 - 2016 indicator stalk fix and removal

Image
This is just a quick post as there is no information on how to fix a K13 Nissan Micra indicator stalk, so model years 2010, 2011, 2012, 2013, 2014, 2015 & 2016. If the indicator, still works but the lights do not flash, you could also try this for your wipers or headlights as the contacts are similar.  (This is considering you have already checked the bulbs and flasher relay. If the hazards work fine then, this is probably what you want to be looking into.  So the indicator on a K13 Micra would not work on the right turn the left worked fine, so did the soft-touch indicator for turning in both directions. After looking for a replacement and discovering that they are not easily obtainable - I noticed that you would not need to remove the steering wheel to fix it. It takes about 20 mins in total to remove and fix. It was well known with the MK1 smart that the hot temperatures, usually of the headlight switch, can cause the hot grease to run and cause a bit of havoc with the ...
Read more

KDE Connect Remote Comands for Mac

Image
If you have an iPhone with a Mac, you are probably pretty used to having connectivity between your Phone, iPad and Mac. If you have a windows device, you can connect you r Phone via  Your Phone Companion - Link to Windows , and it's a similar story if you have a Chromebook and an android device.  But what if you have a Mac and an android device? This is where you can use KDE connect. It's a two-part install: 1) Install the  desktop app  (Still in early release for Mac). 2) Install the  companion app on your Android device. You can read more about what  KDE connect  is and how you can use it on the site's main page.  Anyway, this is not a guide on how to get set up using KDE connect. Instead, I wanted to record some of the remote commands you can run from your Phone on your Mac; the default commands currently in KDE Connect do not work as they are the standard Linux commands.  So here is a set of commands that work in KDE connect on the Mac....
Read more