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....
Labels:

Popular posts from this blog

Useful website performance and load testing tools

http://tsung.erlang-projects.org/ http://httpd.apache.org/docs/2.0/programs/ab.html http://phantomjs.org/ https://developers.google.com/speed/pagespeed/ http://servermonitoringhq.com/blog/how_to_quickly_stress_test_a_web_server https://code.google.com/p/httperf/ http://loadimpact.com/ http://www.paessler.com/webstress http://loaduiweb.org/ http://en.wikipedia.org/wiki/Web_server_benchmarking http://en.wikipedia.org/wiki/Load_testing http://www.loadui.org/ http://www.loadtestingtool.com/index.shtml http://www.appdynamics.com/blog/devops/load-testing-tools-explained-the-server-side/
Read more

Copy files and folders using SCP with spaces in path

Copying data from one system to other with file or folder names that contain spaces in path can be achieved using this guide. In this case I am copying data from Macbook to Windows 10 computer. In order to copy the data easily it is better to use bash commands. Windows computer can support WSL (Windows subsystem for Linux) and you can run one of few linux distributions to use shell commands. I have Ubuntu set up within my Windows 10 using WSL. If you do not have WSL, you can set it up using my guide here . The copy can be performed in two ways: 1) Using SCP Source (MacOs) path: /home/Users/username/Documents/data extract from 2020/ First of all you add escape sequence to the path so it will become:  /home/Users/me/Documents/data\ extract\ from\ 2020/ . While this works on local system for SCP you'll have to double the escape sequences by replacing \ with \\, as below. Figure out your source computer IP address using "ifconfig" command. Now using scp command on target syst...
Read more

VMWare ESXi 6.5 HP Custom Image Upgrade to v6.5U3

I had been escaping the Unhandled Exception error every time I log into my ESXi standalone server running v6.5 on N54L and can't be upgraded to v6.7 due to process compatibility. I have finally found the HP Custom image updated to v6.5U3 which appeared to have the fix built into it. It was quite straightforward update using the l latest 6.5 HP image so as habit I am making notes here for myself and everyone else who may find it useful. First of all I downloaded HP Custom v6.5U3 image  by selecting Offline Bundle. I then placed it in my datastore e.g. ds001. Once copied I ran following command line after I was connected to ESXi using ssh. # esxcli software vib update -d /vmfs/volumes/ds001/VMware-ESXi-6.5.0-Update3-14990892-HPE-preGen9-650.U3.9.6.10.1-Dec2019-depot.zip This command took few seconds or may be minutes but confirmed that updates have been installed and will take effect after reboot. So I rebooted the host and it worked like magic. # reboot I did not place host in main...
Read more