Skip to main content

Convert American Date and Time to British Date and Time - mm/dd/yy hh:mm to dd/mm/yy hh:mm in Microsoft Excel 2010

I had been struggling to get Microsoft Excel 2010 to understand that my data in put is in American date format before I could add some calculations to the date and time. A quick 5 minute googling didn't bring any help so ended up writing a formula and using built in table format helped filling it all the way through the table.

So what you have to do is have a date in American format in cell A2 so that the contents of cell A2 look like below.
11/31/14 23:24

You can copy and paste following formula directly in cell C2 and will simply convert your date/time into British date/time format which you can then use for calculations.

=LEFT(MID(A2,FIND("/",A2)+1,2)&"/",FIND("/",MID(A2,FIND("/",A2)+1,2)&"/")-1)&"/"&LEFT(A2,FIND("/",A2)-1)&"/"&RIGHT(TRIM(LEFT(A2,8)),2)&" "&TRIM(RIGHT(A2,6))

If this doesn't resolve into a recognisable date in your version of excel you can edit field by pressing F2 and enter it should work, however if it still doesn't work you can add vaule formula outside above and then change date format using excel format and should do you the trick.

=VALUE(LEFT(MID(A2,FIND("/",A2)+1,2)&"/",FIND("/",MID(A2,FIND("/",A2)+1,2)&"/")-1)&"/"&LEFT(A2,FIND("/",A2)-1)&"/"&RIGHT(TRIM(LEFT(A2,8)),2)&" "&TRIM(RIGHT(A2,6)))

Simples....

Popular posts from this blog

TrueCrypt on macOS X Mojave 10.14

If you have updated your macOS recently to Mojave otherwise known as verison 10.14 you may not be able to install the last version of Truecrypt in order to access your old volumes encrypted with Truecrypt software. This article will guide you to get this working on your MacOS v10.14 (Mjoave) . Download the package from  https://truecrypt.ch/downloads/  or  https://www.truecrypt71a.com/downloads/ . Find downloaded package using Finder in your HDD/Users/username/Downloads folder and will look like  TrueCrypt 7.1a Mac OS X.dmg . Open file location in Finder and open or double click on  TrueCrypt 7.1a Mac OS X.dmg . This will mount Truecrypt 7.1a and will have Truecrypt 7.1a.mpkg in it. Drag the package T rueCrypt 7.1a.mpkg and drop in your Downloads folder. From Locations in Finder you can eject your TrueCrypt mount. Now go to your Downloads location, find the file  TrueCrypt 7.1a.mpkg , right click and select Show Package Contents . Find the file Contents/distribution.di

Running VNC Server as a Service on Ubuntu

Method 1: VNC Server as a Service on Ubuntu desktop similar to Redhat sysconfig sudo apt-get install vncserver sudo mkdir -p /etc/sysconfig sudo touch /etc/sysconfig/vncservers sudo vi /etc/sysconfig/vncservers # Add following VNC Server instances where username and arguments are defined for each session. VNCSERVERS=" 1:user1 2:user2 3:user3 " VNCSERVERARGS[1]=" -geometry 1280x992 -depth 16 " VNCSERVERARGS[2]=" -geometry 800x600 -depth 8 " VNCSERVERARGS[3]=" -geometry 980x720 " sudo vi /etc/init.d/vncserver # Add below to the service script #!/bin/bash # # chkconfig: - 91 35 # description: Starts and stops vncserver. \ # used to provide remote X administration services. # Source function library. # . /etc/init.d/functions # Source networking configuration. # . /etc/sysconfig/network # Check that networking is up. # [ ${NETWORKING} = "no" ] && exit 0 unset VNCSERVERARGS VNC

Tools you need before you start using Terraform for orchestration infrastructure in cloud

Terraform one of the famous open source tool used for planning, deploying and maintaining infrastructure as a code and the beauty of this tool is that it works across various cloud service providers. Even though I personally like the AWS Cloudformation, it is AWS only and most probably it will always remain limited to Amazon Web Services. It has few advantages over other tools when using with AWS and will be the first to have new AWS features incorporated before other tools and APIs catch up. However if you are not limiting yourself to AWS only or have hybrid environment you may want to use a 3rd party tool compatible for all cloud infrastructures. Terraform being one of them is youngest client only tool and this article will help you setting up your desktop environment and give you understanding of how it works before you can start to code. Prerequisites: Computer running Windows, Linux, Solaris or MacOS operating system. Downloads: Download Terraform directly from the crea