Calculating Time Elapsed in Excel

[google]

Hi Everyone,

How can I calculate the time elapsed between two given times?
For example if I input initial time and date as 20:00 on 03 Jan 06 and
final time and date as 05:00 on 05 Jan 06, I want to calculate the
number of hours elapsed. Moreover I am interested in whole numbers i.e.
8 hours and 31 minutes should round to 9 hours and 8 hours and 29
minutes should round to 8 hours. I need to multiply this time elapsed
with another constant quantity?

Any help would be appreciated.

regards

 

[Guest]

Hi,
Assuming dates are held in format dd/mm/yyyy HH:MM then simple
subtract two dates and multiply by 24.

e.g in c1 put = INT((B1-A1)*24+0.5)

03/06/2006 20:00 (A1)

05/06/2006 20:29 (B1)

C1=48

HTH

 

[google]

Thanks Toppers.

I have copied the formula to other cells in the column. The only
problem is that when there is nothing in the first two columns, I get
0.00. in the third column.
Is it possible that these cells remain blank untill I enter dates/times
in the first two columns?

Thanks

 

[Guest]

Hi,
Ig I understand correctly, in third column you could put:

=IF(Sum(a2:b2)=0, " ", sum(a2:b2))

 

[Guest]

Hi,
Sorry, ignore my previous reply!

Should be:

=IF(OR(A1="",B1=""),"",INT((B1-A1)*24+0.5))

 

[(PeteCresswell)]

Per (e-mail address removed):

How can I calculate the time elapsed between two given times?
For example if I input initial time and date as 20:00 on 03 Jan 06 and
final time and date as 05:00 on 05 Jan 06,

How about this: create a macro to do the heavy lifting and then invoke the
macro in the cell that you want the calc result in.

The macro would look something like this (subject to your modifying the
rounding/truncation):
------------------------------------------------------------------------
Function HoursDiff(ByVal theBeginDateTime As Variant, ByVal theEndDateTime As
Variant) As Long

'PURPOSE: To calculate the difference in hours between two Date/Time values.
'ACCEPTS: - Beginning Date/Time value
' - Ending Date/TimeValue
'RETURNS: Number of hours between the two values
'
Dim myDiff As Long

myDiff = DateDiff("n", theBeginDateTime, theEndDateTime)
myDiff = myDiff / 60

HoursDiff = myDiff

End Function
---------------------------------------------------------------------------

Then, in the cell you want the result in, you put something this into cell A1:

=HoursDiff(E1, F1)

Then type "01/03/2005 20:00:00" into cell E1 and type "01/05/2005 17:00" into
cell F1 and then "45" should magically appear in cell A1.

I could email you the sample spreadsheet - but with one caveat: I don't know
beans about Excel and once I created the function I couldn't find it again...
it's there because I'm getting the results I expect...it's just hiding somewhere
and anybody who knows Excel could probably find it.

 

[swift.mail]

Thanks Pete but I will stick to Toppers solution at this time. Its
doing what I want. One thing that I need now is to lock the column
containing the formula so the user cannot change it . I want the user
to enter the date and time but prevent him from deleting the formula
accidentally.

Thanks and regards

 

[google]

Thanks Pete but I will stick to Toppers solution at this time. Its
doing what I want. One thing that I need now is to lock the column
containing the formula so the user cannot change it . I want the user
to enter the date and time but prevent him from deleting the formula
accidentally.

Thanks and regards

 

[(PeteCresswell)]

Per (e-mail address removed):

One thing that I need now is to lock the column
containing the formula so the user cannot change it .

01) Select all columns via Ctl+A

02) Format|Cells|Protection|Locked=False (you'll have to click the checkbox
twice to make the checkmark go away.

03) Select the column you want protected.

04) Format|Cells|Protection|Locked=True

05) Tools|Protection|Protect Sheet

 

[Guest]

Try using the cell format [h]. You may need to make it in the custom option
in format cells.

 

[google]

Thanks for the help guys.

regards