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)))


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  or . 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

Starting with Python

New to Python, don't feel behind and get started now, it is never too late. Here we have some useful links you will need to get you going. Install Python 3 # sudo apt-get install python3 Change Default binary from v2.x to v3.x sudo update-alternatives --install /usr/bin/python python /usr/bin/python3 10 Install Jupyterlab #pip install jupyterlab

CoVid-19 Statistics and Charts

Statistics based on some specific metrics are listed below. The data is based on daily information from Worldometer and updated manually. Charts will update automatically. Please use links below to get to individual charts and feel free to bookmark this page but do not bookmark direct chart link as it may change. These charts compare statistics for countries including China, Italy, UK, US, Pakistan & India. (Does not work in Safari Mobile) Please click on links below for specific chart, more to come. Death Doubling Rate Chart showing death rate doubling in x number of days. Survival Rate This chart shows trend of patients successfully recovered vs total closed cases. Death rate comparison Comparison of deaths per cases every day showing trend comparison and days behind. Cases reported No of cases reported, how far they are behind each other and trend. Deaths reported No of deaths reported with trend comparison betwee