Convert Time to Hours in Excel

Standard

I’ve found myself having to convert time in Excel stored as hours, minutes, seconds (e.g. 3:42:09) into hours in decimal format (e.g. 3.70) for invoicing my time and every month I end up googling this.

The method I use is a formula in Excel using the Excel Time functions (HOUR, MINUTE, and SECOND).

=HOUR(CELL) + MINUTE(CELL) / 60 + SECOND(CELL) / 3600

Replace CELL with the appropriate cell in your Excel workbook (e.g. A2).

 

Solution to HTTP 400 Bad Request when POSTing to WCF RESTful web service

Standard

Here’s the first post.  While this first post on my iPhone development blog has nothing to do with the iPhone, it’s something that I’d like to share with you all as it took me a few hours to get this one totally figured out.

The first post I’ll share is a scenario of a problem I was having with communicating with a RESTful WCF service. For some reason all I was getting back from the service was an HTTP 400 – Bad Request response when attempting to make a POST to a url.

So, here’s what I was attempting to do.  I created a simple WCF RESTful web service.  This web service will be used for all the communication between the iPhone’s and my server.

I created a simple method defined as follows:

public interface UsersService {
[OperationContract]
[WebInvoke(Method = "POST", UriTemplate = "users",
RequestFormat = WebMessageFormat.Xml,
ResponseFormat = WebMessageFormat.Xml,
BodyStyle = WebMessageBodyStyle.Bare)]
CreateUserStatus CreateUser(WebUser user);
}
view raw UsersService.cs hosted with ❤ by GitHub

Looks strait forward enough.  This definition means that the method will respond to POST actions against the url /users.  The RequestFormat is XML and the ResponseFormat is XML.  The message format of the body of the request is Bare, meaning that it will just contain the raw XML.

Ok, now that the method is defined and I waived some magic under the hood of the interface definition to do something with the request.  I bring up my trusty HTTP application to send some requests to the service and see what happens.  (the trusty HTTP app I was using if you were wondering is: HTTP Client).  Now, I spin up and send some requests to the service.

Firstly, you need to make sure that you specify the content type and a couple other headers needed for the requests.

REQUEST

POST /IPhoneDataService.svc/users HTTP/1.1
Content-Type: text/xml
Accept: application/xml
User-Agent: Mozilla/5.0 (iPhone; U; CPU like Mac OS X; en) AppleWebKit/420.1 (KHTML, like Gecko) Version/3.0 Mobile/4A93 Safari/419.3
<?xml version=”1.0″ encoding=”utf-8″?>
<WebUser>
<Email>test2@test.com</Email>
<JoinDate>2009-11-19T18:04:02.9990737-05:00</JoinDate>
<Password>*******</Password>
<UUID>039af7a3-832d-4a91-a058-dfa10d2b223e</UUID>
<Username>theUsername</Username>
</WebUser>
view raw iphone-post.txt hosted with ❤ by GitHub

RESPONSE

HTTP/1.1 400 Bad Request
Connection: close
Date: Fri, 20 Nov 2009 03:36:50 GMT
Server: Microsoft-IIS/6.0
X-Powered-By: ASP.NET
X-Aspnet-Version: 2.0.50727
Cache-Control: private
Content-Type: text/html
Content-Length: 2890
<?xml version="1.0" encoding="utf-8"?>
<HTML>
<HEAD>
<STYLE type="text/css">
#content{ FONT-SIZE: 0.7em; PADDING-BOTTOM: 2em; MARGIN-LEFT: 30px}
BODY{MARGIN-TOP: 0px; MARGIN-LEFT: 0px; COLOR: #000000; FONT-FAMILY: Verdana; BACKGROUND-COLOR: white}
P{MARGIN-TOP: 0px; MARGIN-BOTTOM: 12px; COLOR: #000000; FONT-FAMILY: Verdana}
PRE{BORDER-RIGHT: #f0f0e0 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #f0f0e0 1px solid; MARGIN-TOP: -5px; PADDING-LEFT: 5px; FONT-SIZE: 1.2em; PADDING-BOTTOM: 5px; BORDER-LEFT: #f0f0e0 1px solid; PADDING-TOP: 5px; BORDER-BOTTOM: #f0f0e0 1px solid; FONT-FAMILY: Courier New; BACKGROUND-COLOR: #e5e5cc}
.heading1{MARGIN-TOP: 0px; PADDING-LEFT: 15px; FONT-WEIGHT: normal; FONT-SIZE: 26px; MARGIN-BOTTOM: 0px; PADDING-BOTTOM: 3px; MARGIN-LEFT: -30px; WIDTH: 100%; COLOR: #ffffff; PADDING-TOP: 10px; FONT-FAMILY: Tahoma; BACKGROUND-COLOR: #003366}
.intro{MARGIN-LEFT: -15px}
</STYLE>
<TITLE>Request Error</TITLE>
</HEAD>
<BODY>
<div id="content">
Request Error
HTTP/1.1 400 Bad RequestConnection: closeDate: Fri, 20 Nov 2009 03:36:50 GMTServer: Microsoft-IIS/6.0X-Powered-By: ASP.NETX-Aspnet-Version: 2.0.50727Cache-Control: privateContent-Type: text/htmlContent-Length: 2890
</div>
</BODY>
</HTML>

That’s it, end of story, Go to Jail, Do Not Pass Go!!  WCF does not give you any error messages, nothing, NADA!  How do you figure out what’s wrong?

I spent a lot of time searching on the web, trying to find out, is it an error in definition of the method in the service, is it hitting the service at all, put a breakpoint in the service, nothing.  The error is being thrown by the WCF runtime, so what gives??  What’s the real error?

After trying everything I could think of I did the one thing that actually makes sense.  I went into my application, and wrote a UNIT TEST.  I started from the beginning, create valid XML representing the DataContract object I want to send through the WCF service.

Voila, problem solved!  The problem turned out to be that I wasn’t specifying the namespace on the XML representation of the object I was sending in the body of the POST message!!  Now, why couldn’t WCF have told me that?  Why not give me a friendly error message in the response (I have the setting enabled to show full error details in the WCF XML configuration in the web.config file).

Resolution

Just include the namespace on the XML object I was sending in the body of the request.  So, with the added namespace(xmlns=“http://development.svnanalytics.com/contracts” xmlns:i=“http://www.w3.org/2001/XMLSchema-instance”) Everything just worked again!

Here’s the working Request and Response.  Note the namespace now specified on the XML in the body:

Request

POST /IPhoneDataService.svc/users HTTP/1.1
Content-Type: text/xml
Accept: application/xml
User-Agent: Mozilla/5.0 (iPhone; U; CPU like Mac OS X; en) AppleWebKit/420.1 (KHTML, like Gecko) Version/3.0 Mobile/4A93 Safari/419.3
<?xml version="1.0" encoding="utf-8"?>
<WebUser
xmlns="http://development.svnanalytics.com/contracts&quot;
xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
<Email>test2@test.com</Email>
<JoinDate>2009-11-19T18:04:02.9990737-05:00</JoinDate>
<UUID>039af7a3-832d-4a91-a058-dfa10d2b223e</UUID>
<Username>theUsername</Username>
</WebUser>

Response

HTTP/1.1 200 OK
Connection: close
Date: Fri, 20 Nov 2009 02:51:10 GMT
Server: Microsoft-IIS/6.0
X-Powered-By: ASP.NET
X-Aspnet-Version: 2.0.50727
Cache-Control: private
Content-Type: application/xml; charset=utf-8
Content-Length: 242
<CreateUserStatus
xmlns="http://development.svnanalytics.com/contracts&quot;
xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
<CreatedUser>
<Email>test2@test.com</Email>
<JoinDate>2009-11-19T18:04:02.9990737-05:00</JoinDate>
<UUID>039af7a3-832d-4a91-a058-dfa10d2b223e</UUID>
<Username>theUsername</Username>
</CreatedUser>
<Success>true</Success>
</CreateUserStatus>