Finding Hours in two dates with times.
Hello,
I'm trying to calculate the hours between two date with times.
Ex:
Start 01/01/17 23:50 , end 01/03/17 08:30. I have made a table with csv so that the date and times are separated. But im not sure how to do the calculations. In some cases it would return a neg number that I must calculate as well. ex: Start 02/01/17 09:00 end 01/30/17 00:30
any help is greatly app.
Thanks,
Steve
Hi,
Does that mean that you have 4 cells, two with dates and two with times.
Perhaps upload the workbook so that we can be certain what you're using. Manually add the results you expect to see.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
why would the END be before the START ?
Hours = ((End Date+End Time)-(Start Date+Start Time))*24
and format the cell as
custom
[H]:MM
perhaps attach a sample file
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Re: Finding Hours in two dates with times.
Sorry cant fine how to upload the file.
Id expect to see:
| A | B | C | D | E |
Assignment Date Time Pickup Date Time Hours
01/01/17 | 20:30 | 01/02/17 | 22:30 | 26.0
01/04/17 | 20:30 | 01/03/17 | 20:30 | -24.0
Some times pick up will occur before assignment. So that would calculate to a negative number. Currently I have both versions of the file being date&time format together, and a csv that I made separating them as you see here in this example above.
To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"
Per your instructions I clicked upload and I get the "your not logged in or have permission to do this." screen. Is uploading a paid feature?
What answer do you expect for the second example. -24 hours is mathematically correct given that you're deducting A from B
It's only with times that Excel complains because it doesn't understand negative time differences. The usual approach I adopt with times is to test for an end time that's earlier than a start and add 24. e.g. if B1 is start time of 20:30 and D1 is end time is 18:30 then
=(D1-B1)+IF(D1<B1,24,0)
Thanks for all the time spend responding. I've found what I'm looking for on another thread. In my line of work tracking the negative hours is a way to see an advance. and positive hours over a certain thresh hold late.
The formula I used was Hours =((End_Date+End_Time)-(Start_Date+Start_Time))*24 worked perfectly to what I needed. Thanks for the reply's and effort.
As i posted in #3 (10mins after you started the thread ) above then
anyway, glad its resolved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks