Excel Formulas - Help

“I'm stumped. Any excel nerds out there wanna lend me a hand?

Cell A1 "2:00 PM"
Cell B1 "4:30 PM"

I want Cell C1 to be the amount of time that elapsed but not in HH:MM. Instead, I want it in decimal form to represent the number of hours. So, 2.50 hours. I tried a few elapsing-time formulas and got full hours in number form (it said 2 hours not 2.5, it seemed to ignore the 1/2 hour) and HH:MM in time form (2:30 but that doesn't work for what I need).

To add another layer of confusion to my formula problem - What I really really want is for the number in C1 to be rounded up to the nearest quarter. So, if the times were 2:00 PM and 4:25 PM, I'd like my totals column to read 2.5, despite the fact that the total time was actually 5 minutes shy.

Anybody??”

“Have you tried using military time (2:00 PM = 1400, 4:30 PM = 1630)?”

“I know this does not directly answer the question... but rather than trying to design your own spreadsheet, would it make sense to use one of the time keeping apps that are available, many of them as freeware? Just a thought. I don't like reinventing the wheel.”

“kleetn - no, I haven't. The column needs to show 2:00 PM. But, I suppose I could find a way for the formula to recognize that as 1400. Hmmmm...

Ramb - I wish I didn't have to reinvent the wheel... I work for a non-profit, government funded org. I don't get much of a say and their expectations aren't always realistic. The spreadsheet I created and use (and am trying to adjust) is not only for tracking time. There is mucho other info and other formulas.”

“I googled this...

http://www.cpearson.com/excel/datearith.htm”

“I figured it would be an IF statement, but I never do them.”

“Maybe this link will help?

http://office.microsoft.com/training/training.aspx?AssetID=RC060796661033”

“In C1 try this forumla: =(B1-A1)*24

Regarding number formatting, format columns A & B for "time" and format column C to be "general".
last edited: 4/14/08 9:02:53 AM”

“Thanks. I did a google but must not have used the right key words. I'll check out both of those links and see if I can't find exactly what I need to do!”

“Ruby - I did that formula and it kept rounding it to the nearest hour. But, I don't believe I tried formatting c to General. I'll see if that will work.”

“an anonymous tter emailed me a formula that worked perfectly...

=(CEILING(B1, 15/1440)-CEILING(A1, 15/1440))*1440/60

Thanks, everyone!”

“I usually just ask one of the "young kids" around the office - coding stuff in excel is apparently taught as a second language these days!”

“Looks like you have it. We use that formula on our time sheets.”

“an anonymous tter emailed me a formula that worked perfectly


That was no anonymous TTer, that was Sarge! Thanks, Sarge!”

“"Who was that masked man anyway." The Lone Ranger”