Help with calculating time

G

Gary Hopkins

All - I am in desperate need of figuring out how to do something. What I
need is a way to figure out the amount of time (in hours/minutes) between
two points.

I have a few problems that I need to solve:

1) My times and dates are currently in two separate fields. How can I
combine them together?

2) After I get the time&date merged into one field, how can I calculate the
amount of time between 2 cells?

If anyone has any solutions for any or all of this I would greatly
appreciate it!

Gary
 
F

Frank Stone

This will combine the date and time togeather
Range("D2").Value = Format(Range("B2"), "short date")
& " " & Format(Range("C2"), "long time")
 
S

Steven M (remove dirt and invalid to reply)

All - I am in desperate need of figuring out how to do something. What I
need is a way to figure out the amount of time (in hours/minutes) between
two points.

I have a few problems that I need to solve:

1) My times and dates are currently in two separate fields. How can I
combine them together?
Click to expand...

There are several ways, one mentioned in another response. I had
another idea that would take some time to think about.

One thought: Why are the time and date in two separate fields? Can
you combine them beforehand, to produce a cell that has the combined
time/date?

In a way, the time and the date are simply two views of the same
information. You can have a cell with this value:

38209.0957393721

If you give it one format, it will display as:
10-Aug-04

The same number, with another format, will be visible as:
2:17:52 AM
2) After I get the time&date merged into one field, how can I calculate the
amount of time between 2 cells?
Click to expand...

If you subtract one cell from another, that gives you the difference
between the two times. 1 = one day. You will need to format the cell
containing the difference (result of the subtraction) with a numeric
format, something like General or Number (with decimal points).

If you want hours, multiply the result by 24. That will give decimal
hours. If you want a format like hh:mm:ss that can be done, but it's
trickier.



--
Steve M - [email protected] (remove dirt for reply)

"If this is coffee, please bring me some tea. If this is tea,
please bring me some coffee."
- Abraham Lincoln
 
M

mzehr

Hi Gary,

If the date is manually input in column a (and is properly
formatted as a date), and the time is in column b (and is
properly formatted as time, you can simply add the two
together ie =a1+b1.

For date/time difference calculations check out the
following excellent web sites, as there are different
factors to consider

http://www.cpearson.com/excel/datedif.htm
Chip Pearson
http://www.cpearson.com/excel/DateTimeEntry.htm
Chip Pearson
http://www.mvps.org/dmcritchie/excel/datetime.htm
David McRitchie

HTH
 
G

Gary Hopkins

Thank you for the reply it is greatly appreciated! However, I have now ran
across one more problem with my data.

Unfortunately my time fields are being displayed in text. The report that
I'm getting the data from is taking it in as a number. Hhmmss. I am then
taking this number and doing an INT(cell)/100 to turn it into a number field
and dropping off the seconds since I only care about the hours and minutes.
After that, I am using the concatenate function to get a semi-colon between
the number resulting in an hh:mm format. When I do this, the data now turns
into text rather than a usable time. Is there a way for me to convert this
to time or is there another way I can do this whole procedure so my output
is hh:mm in time format? Thanks again.

Gary


"Steven M (remove dirt and invalid to reply)"
 
R

Ron Rosenfeld

All - I am in desperate need of figuring out how to do something. What I
need is a way to figure out the amount of time (in hours/minutes) between
two points.

I have a few problems that I need to solve:

1) My times and dates are currently in two separate fields. How can I
combine them together?
Click to expand...

If the dates and times are in an Excel format (e.g. date = # of days since
1/1/1900 or 1904) and times = fraction of a day, then with the Date in A1 and
the time in B1, you combine them by adding =A1+B1. You can format the result
as d-mmm-yyyy h:m AM/PM would show 10-Aug-2004 2:57 PM

2) After I get the time&date merged into one field, how can I calculate the
amount of time between 2 cells?
Click to expand...

If your first combination is in A1 and your later combination is in B1, then
the time between them is =B1-A1. Format the result as [h]:mm to get the time
in hours and minutes


--ron
 
M

Myrna Larson

I assume the date fields are being recognized by Excel as dates, and you have
a date in A1, the time in hhmmss (no punctuation) as a number in B1. This
formula gives you a combined date and time:

=A2+TIME(B2/10000,MOD(B2/100,100),0)
 
Top