Need formula dealing with time, hours & minutes

**scarames** · 02-01-2010, 01:03 PM

I've got a worksheet that deals with dates and time. Similar to a checkbook, I need to take the balance and subtract what's taken from that. But the writer copied this from another software program and it's in this format. How do I convert it to a time format? Or is that what I really need? Right now the taken and balance are both in a general format. I just need to know on any of the given days what the balance of this person's time was in hours and minutes. Thanks for any assistance you may be able to give. Excel 2007. BTW, I don't really know how to write code.

Date	       Source	                Taken	Balance
1/1/2009	       Balance Fwd		                480h00
1/5/2009	       Timekeeping Usage	1h25	
1/22/2009	       Timekeeping Usage	10h00	
1/23/2009	        Timekeeping Usage	7h00

**DonkeyOte** · 02-01-2010, 01:09 PM

The format isn't very clear - could you post a sample file ?

In general you can convert 1h25 to time by simply substituting the "h" for a colon and coercing the result, eg:

D2: =--SUBSTITUTE(LOWER(C2),"h",":")

would convert 480h00 to 480:00 when formatted as [h]:mm

If the remainder should be the balance from that figure then:

D3: =D2-SUBSTITUTE(LOWER(C3),"h",":")
copied down

does that help ?

**scarames** · 02-01-2010, 02:15 PM

DonkeyOte,
Thank you. That was much easier than I was thinking. Everyone kept telling me it wouldn't work and I just decided they were right and didn't really try it on my own enough. I just did a search and replace and replaced the h with the colon and then a formatted the first list of times as a time format then used simple subtraction. I checked the balances and they were right. Should have trusted my instincts to begin with. Thanks much for your quick help.
Sue