I need formula for finding % of times... i have an employee that has worked 4.52 hours and was on break 16.50 minutes... need the percentage of the 4.52 he was on break.
Cells are in custom [h]:mm:ss format
4:52:45 | 16:50
I know simple math but cell format seems to messing me up as did and still doing with my previous problem... i cant change format of cells because im sending data to other parts of sheet in this format
Thankyou in advance!
Last edited by keith740; 04-26-2015 at 05:08 PM.
Have a look at this.
im trying to complicate it way too much i think, that was simple... thank you!
But why when i run same formula on the next employee i get 308%
1:01:21
3:09
308.1%
It depends how you entered 3:09. If you enter 3:09, Excel will interpret this as 3:09:00. If you meant 3 minutes, then you need to enter 0:3:9
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
you are right that was the problem! and worked great
this arises another problem
the original data i copy paste from has minutes in wrong format and some but not all "total"time See attached
is there a formula to recognize these columns are for hours the other for minutes, or do i have to manually change each cell
Last edited by keith740; 04-26-2015 at 05:09 PM.
Nothing attached. That's my usual mistake...
my bad, attached now
when you have xx:yy are they hh:mm or mm:ss? I'm signing out now. If you have replied & no-one else has sorted you out; look back in your Monday morning. It'll be done by then.
the pause column:correct ones are h:mm... the incorrect ones are [h]:mm:ss... when checking i figured out just change format and fix this column
but the time column with correct in h:mm:ss and incorrect in [h]:mm:ss, when i change column format it give me completely different times...
25:27:00 0:36 this should read 25 minutes worked 36 seconds pause, when i change column format it sais he worked 1.25 hrs 36 seconds pause
is there a formula to determine if over 8 hours to remove last :00
I've had a look at this and want you to check if you're right. Dates and times are simply numbers. As I type this it's 27-04-2015 07:03. However, while that means something to you and me, it means nothing to Excel. To Excel, it's 42121.29375208.
The value in (for example) F10 appears to be 42:21:00, formatted as [h]:mm:ss (i.e. 15:20:00, formatted as hh].mm.ss). Are you 100% certain that it SHOULD be 42:21 mm:ss???
100% yes... these are all time taken from an 8 hour shift.
The website i pull the data from is where the error occurs... only does this when employee works less than 1 hr... say he works 20 minutes, if i dont catch the mistake he gets paid for 20 hours
maybe could do something like (im kind of new to excel so i know im wrong on formula) =IF(>08:00:00)LEFT-3
Hopefully, others will correct me if I'm wrong; (cell F6) - the screen shows 39:05:00. Those figures don't really exist within Excel. It sees this as 1.62847222222222 One day and 0.62847222222222 days after 01-01-1900. 0.62847222222222 of a day = 15:30, which is how Excel reads this as a time. The 39:05 that you see is 24 hours plus 15:30. What you have is what you have!!
So, take a step back. In what format do you download this stuff from the web? What formatting did you do before you posted the data here? can you post some of the source data, indicating which cells subsequently cause problems.
there is a download tab... i click that and it download labeled agent performance details.csv and open in excel, i then copy paste entire sheet to my template which pulls data... i think you may be right it is what it is ... attached is yesterdays with highlights
Is this what you want?
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
In opening/importing the .csv, do you do "anything fancy"? Can you post the CSV?
nothing fancy... i click download... window pops with "what should firefox do with this file" with options of open with microsoft excel (default option) and save file... i open with excel
that is exactly what i want and i see it apply to the time column as well
is there a way distinguish the cells in the pause column that are over 1 HR and leave them as is
And the time column only apply this to cells that are over 8 hours?
Ty very much guys
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks