Geeks With Blogs

Geekus Con Livus Malcolm Anderson's home for Geeks With Lives
I'm working on an Excel 2010 spreadsheet and I'm trying to put in durations for some tasks I want to schedule.

The interesting thing is that up until a few minutes ago, I couldn't do it.

I was entering in "47:00" and excel was (and still is) converting it to "1/1/1900 23:00:00"

In my mind, I want the value to be 47 minutes, but for the life of me I cannot find a fix for this behavior.

Here's the weirdest thing, I haven't had this problem in the past.  Usually I put in times, add them up and they work like magic.  Put in 18 entries of 20 minutes each, total them and excel will usually tell me that it's a total of 6 hours.

No problem.

Today, problem.

Here's the weird bit:
As I was writing this post, I got it to work.
By formatting the column as custom "[hh]:mm" and summing the columns, I can get total times.
But the times are still being formatted into dates if I look at the underlying data. 

Bottom line, if you need to calculate durations, you can, but don't look too closely at what is happening underneath the covers.
Posted on Monday, July 9, 2012 3:24 AM Stupid Code Tricks | Back to top

Comments on this post: Excel duration converts to date

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Malcolm Anderson | Powered by: