Converting Time to a decimal and ...

**zen3k** · 07-20-2009, 02:03 PM

Ok i'm wondering if this is possible. I'm trying to make a formula that will convert time into a decimal. This is used for keeping track of hours. Example: Billy bob joe signed in from 2:12 to 3:00. so for me to track the hours i have to round to the nearest 1/4 which makes 2:15 to 4:00. which would give them 1.75 hours for that day.

is there anyway for excel to do this for me. Hope this makes sense, and as always thank you for your time guys.

**shg** · 07-20-2009, 02:07 PM

=round((b1-a1)*96, 0) / 4

**martindwilson** · 07-20-2009, 02:13 PM

Try
=round((b1-a1)*24/0.25,0)*0.25
essentially the same since 24/0.25 =96 anyway but perhaps gives a clearer picture of what was done.

**zen3k** · 07-20-2009, 02:27 PM

OMG this works perfect, but is it possible to explain the formula. *24/0.25,0 is throwing me off.

**zen3k** · 07-20-2009, 02:56 PM

Hmm, well i thought it worked perfect, until in put in an entry where someone came in at 10:00 to 3:00 it gave me 7 hours. After looking at the edit formula box it was showing AM instead of PM. Is there anyway i can fix this without manually changing AM to PM for times after 12:00 pm.