SUM time shows 01:95 instead of 02:35.
Can someone take a look at this spreadsheet and tell me what the heck?
I cannot figure out how to make the time "roll-over" after 60 minutes.
The specific tab is 'Statistics' and under Time/Average/TOTAL...
This seems so easy, but I can't seem to get it...SDGR Logsheet.xlsx
Hi, long time no post (1/12/2009)
What answer were you expecting there?
Looking at the data on sheet1, your "time" is actually not time, it is just a number in 100's or 1000's. This means that none of your calcs are producing time...for instance Invoices S3 is giving an anser of 200 for a "time" of 12:35 to 14:35.
I will take a look and see what I can work out for you
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
The best would be if you could just have times in H and I, but if you cant, then try this. In S3, copied down...
=IF(H3="","",TIME(LEFT(I3,LEN(I3)-2),RIGHT(I3,2),0)-TIME(LEFT(H3,LEN(H3)-2),RIGHT(H3,2),0))
format this to TIME 37:30:50, although this is for visula reference only.
This will give you a total in T3 of 8:18:00 (if you use the format above)
Then on sheet2, you should end up with 2:04:30 (again, using the above format)
On a side-note, you dont need to use () for simple calcs like =(E7)/(B11) you can just use =E7/B11
Let me know how you make out?
Invoices!S3: =TIME(INT((I3-H3)/100),MOD(I3-H3,100),0) ...and format cell as hh:mm (as well as your total and average on Stat's sheet) and copy down.
Scratch that formula! The I3, H3 values have to be times before they are subtracted because of the 0-60 minute vs. the 0-99 decimal disparity... e.g. if time out minutes are less than time in minutes, such as 201 and 159 respectively, you get a difference of 42 minutes rather than 2. Use the following instead...
=TIME(INT(I3/100),MOD(I3,100),0)-TIME(INT(H3/100),MOD(H3,100),0)
PS: This assumes the Time Out is always the same day. Let us know if it can be other than same day.
Last edited by jhren; 08-08-2013 at 09:10 PM.
Or
=TEXT(I3, "0\:00") - TEXT(H3, "0\:00")
Last edited by shg; 08-08-2013 at 09:29 PM.
Entia non sunt multiplicanda sine necessitate
Re: SUM time shows 01:95 instead of 02:35.
GREAT! Thank you all for helping me through this!!!!!
* SOLVED *
(now if I can just figure out how to officially mark this as solved.)
To mark "solved" click on thread tools above your first post, and you'll see the option to mark as solved.
How can I modify .
=TIME(INT(I3/100),MOD(I3,100),0)-TIME(INT(H3/100),MOD(H3,100),0)
to be used with times that include seconds? I need something like 14:45:12 - 8:03:02
A good place to start is by reading the forum rules, and then starting your own thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks