Hi All,
I have been trying to solve this riddle but only getting so far and have attached the example.
I can calculate between dates and between times quite easily but I am struggling when I need to calculate total time elapsed when it goes over 24 hours.
Anything under 24 hours is fine and calculates as I would expect.
Hopefully the attachment show what the problem is.
Many thanks if anyone can resolve this.
Try changing your number formats in the "Cell Dialog Box" (ctrl + 1). See if my attached file works.
Excel Forum Query 1 - Call Time Duration Altered.xls
BrownBoy
If happy, mark "SOVLED" & add to "REP"
Hi BrownBoy,
Many thanks for the reply and attachment but unfortunately that does not work for my sheet as I have the date and times split across 4 separate columns. I think in your example it pulls the date and time into a single cell? With my spread sheet the data is imported and breaks down the start date, close date, start time , end time into separate fields and this is where I have the problem.
Re: Calculating Duration Between Two Dates & Times Which Are Split Across Seperate Columns
SOLVED
Hi BrownBoy,
Just figured this out and your formula works. I just needed to create two new columns to pull the date and times together in a single cell and then apply the formula to calculate those columns and it calculates duration perfectly.
Thanks for your help.
Perhaps format column F as [h]:mm:ss and use this in F2 and copy down.
Formula:
Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi I am having a similar issue yet mine situation doesn't involve dates. I need to determine the remaining # of months between 2 dates. I have a loan portfolio and need to determine the Average # of months to maturity.
So for example using todays date 20140508 and the loan maturity date of 20120102. I've tried a few yet the date format that comes across in my query seems to be part of my issued. Any guidance?
cucjw
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks