Calculating Date AND Time Differences

L

Lea777

:confused:

Within 4 columns, I have a series of:
Start Dates / Start Times / End Dates / End Times
I need to create a formula which will give me the sum total of th
difference between the data sets.

For example, the time difference between 8am on the 20th March and 1p
on the 21st March is 29 hours OR 1 day and 5 hours.
I need a formula which will calculate this for me.

I have driven the Paper Slide help function crazy by asking in numerou
ways how to do this and I've also searched the forums as well as askin
my resident Excel guy at work, but I cannot locate a solution.... Ca
anyone help ??
Regards, Lea77
 
P

Peo Sjoblom

Start date in A2, start time in B2, end date in C2 and end time D2

03/20/06 in A2, 08:00 AM in B2, 03/21/06 in C2 and 01:00 PM

=(C2+D2)-(A2+B2)

format custom as [hh]:mm will return 29:00, format as d hh:mm will return 1
05:00

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
P

Peo Sjoblom

I might add that if you need to calculate an hourly rate you need to
multiply the result with 24 then the rate

assume the formula is in F2

=F2*24*rate

format as currency

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




Peo Sjoblom said:
Start date in A2, start time in B2, end date in C2 and end time D2

03/20/06 in A2, 08:00 AM in B2, 03/21/06 in C2 and 01:00 PM

=(C2+D2)-(A2+B2)

format custom as [hh]:mm will return 29:00, format as d hh:mm will return
1 05:00

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




Lea777 said:
:confused:

Within 4 columns, I have a series of:
Start Dates / Start Times / End Dates / End Times
I need to create a formula which will give me the sum total of the
difference between the data sets.

For example, the time difference between 8am on the 20th March and 1pm
on the 21st March is 29 hours OR 1 day and 5 hours.
I need a formula which will calculate this for me.

I have driven the Paper Slide help function crazy by asking in numerous
ways how to do this and I've also searched the forums as well as asking
my resident Excel guy at work, but I cannot locate a solution.... Can
anyone help ??
Regards, Lea777


--
Lea777
------------------------------------------------------------------------
Lea777's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=32692
View this thread:
http://www.excelforum.com/showthread.php?threadid=525103
Click to expand...
Click to expand...
 
B

Biff

Hi!

(End Date + End Time) - (Start Date + Start Time)

Format the cell as [h]:mm for a return of 29:00

((End Date + End Time) - (Start Date + Start Time))*24

Format the cell as GENERAL for a return of 29.

Biff
 
H

harishkumar09

I have a different problem.I work in the BPO industry and I have an
excel spreasheet that keeps track of every agents time on call.I want a
spreadsheet column which will automatically display the current time
when the agent clicks on the cell.Then I need another column in which
the agents will click after they finish the call.And this cell should
automatically display the current time.I will then use the time
difference functions to calculate the time on call.Can anybody help ?
 
C

CarlosAntenna

The current time can be entered by pressing "<control>+<shift>+;". To do it
by clicking on a cell would involve programming an event driven macro. You
could ask in the programming group.

--
Carlos

"harishkumar09" <[email protected]>
wrote in message
news:[email protected]...
 
H

harishkumar09

Thanks Carlos.Let me also tell you we use Star Office in our workplac
and do the macros suggested in the programming section work fo
StarOffice as well
 
Top