February 21, 2007

Decimal Time

The other day I was asked for a formula to convert time into decimals. I'm not quite sure if it is fit the bill or not but this is what I came up with.

=HOUR(A1)&"."&SUBSTITUTE(MINUTE(A1)/6,".","")

You can see how it turns out.


Well, okay, I don't work with time that often, it's usually dates that I worry about.....anyway I thought this formula that rounds time to 15 minute increments might be useful, at least the payroll where I used to work was calculated this way.

=VLOOKUP(MINUTE(A1),{0,0;15,0.25;30,0.5;45,0.75},2,1)

Does seem a little better I guess.


But you will notice that I just show the decimal part (the minutes), not the hours. What is the norm here?

And, finally, here is a simple formula to show hours as 1 - 12 regardless of AM or PM.

=IF(MOD(A1,12)=0,12,MOD(A1,12))

How about you? What time formulas do you use? Don't forget I've got quite a few date and time formulas here. I'm always happy to add to them and give you credit so don't be shy ;-)  

Posted by andrewe at 14:48

February 12, 2007

Home Sweet Home

Yep, I'm back in the land of Oz at last.

And life is pretty hard... here is a shot of my new office (as you can see, I have to rough it outdoors...)



Actually I am staying with relatives at the moment, but we are looking for a place near the sea too.

I'm looking forward to getting used to this ;-)
  
Posted by andrewe at 20:38