Date/Time formula

**soma104** · 02-16-2010, 11:47 AM

Hi,

I have been asked to make some ammendements to a workbook currently in use.

I have to calculate elapsed time, and my formula is giving me odd results. I have a start time (cell B5), and an end time (cell F5). The formula being used was =IF(F5>0,((F5-B5)*24)) which gave the correct result. The cell is formatted as general. It didn't alot for the situation where the end time has not happened yet.

I changed the formula to =IF(F5>0,((F5-B5)*24),(K5-B5)*24) where cell K5 is the current time. The results are coming out with 6 - 9 decimal places. It makes no sense as I've tried setting the times to be exactly 24 or 48 hours to the minute. I've also tried replacing K5 in the formula to now(), but it didn't help.

Does anyone have any ideas?

Soma

**Richard Buttrey** · 02-16-2010, 11:50 AM

Hi

An uploaded sample workbook is worth a thousand words.

Rgds

**soma104** · 02-16-2010, 12:04 PM

You're right Richard,

I've attached a copy of the workbook. I've highlighted in red the fields that are giving me trouble.

Soma

**ptm0412** · 02-16-2010, 12:24 PM

Why don't you round the result?
H5 = IF(F5>0, ((F5-B5)*24), ROUND((G5-B5)*24, 1))

Or if you want to show the result as hh:mm, you could format it as custom: [hh]:mm

**soma104** · 02-16-2010, 12:33 PM

Thank you,

The rounding off worked.

I had tried formatting it as [hh]:mm, but that made a bigger mess. I have no idea why.

No problems though. The rounding off gave me the results I needed.

Thanks again
Soma

**marc5354** · 02-16-2010, 12:58 PM

please find, hope this will help

...Marc

**soma104** · 02-16-2010, 01:19 PM

Thanks marc5354,

I will try both methods.

Soma

**marc5354** · 02-16-2010, 01:24 PM

hmm i m not sure, but think it works...

and let me know. if its working as according to your wish..

...Marc

**ptm0412** · 02-16-2010, 02:39 PM

I am sorry for saying not clearly. If you use the custom format [hh]:mm, the formula will be:
H5 =IF(F5="",G5-B5,F5-B5)

See attached file.

**soma104** · 02-16-2010, 04:15 PM

Thank you all for your replies,

I've tried the differnet methods, and here is what I'm coming up with. Both methods work find in the excel sheet, but I can't get the right format to come up in my form.

I have a form that provides the data from all the calculation, and it doesn't matter which method I use, when I reference cell H5 in the form, it comes out looking horrible. In my current entry, cell H5 shows 13:12 hours, in the form it comes up as 0.55000000000291.

This is the code in the Form

Please Login or Register  to view this content.

Where txtTimeSpent.Value is the value in cell H5

Almost there, but not quite.

Soma

**marc5354** · 02-16-2010, 04:23 PM

Hello Soma

Can you please help...????

...Marc

**soma104** · 02-16-2010, 04:27 PM

Marc,

You're suggestion work perfectly in the excel spreadsheet. What I'm having an issue with now is putting that time into a form using VBA.

Soma

**marc5354** · 02-16-2010, 04:31 PM

Anyway that is good, i am having some other issues in formula to get the difference between file received time and how to calculate remaining TAT (Turn Around Time).

Can you please help, If Possible

...Marc

**kwray49** · 01-05-2011, 10:37 PM

This time sheet is not calculating time the same even though the formula's are exactly alike. It involves minutes converted to tenth's of an hour. One place it will calculate 15 minutes as .2 hours and others it will calculate 15 minutes to .3 hours. I have looked at it until I'm blind.

I have the calculate options setup to calculate automatically and set precision as displayed is checked.

I'd sure appreciate someone taking a look at it and letting me know what I need to do to fix this.