Sorry, but we just had an analyst leave and were left holding the bag trying to figure out a few calculations... Long time SysAdm - but I don't do stats..
The problem is calculating out the time a job takes ... It should be easy enough as is should be stop minus start time ... The problem is the date fields looks like this 2-Jun-15 03.25.15.820000000 PM ... Unfortunately, I can't get the output in another format... I have tried doing a custom time field but no luck..
Help
Thanks in Advance
J
Hi, welcome to the forum
See if this will get you started...
=DATEVALUE(LEFT(D2,FIND(" ",D2,1)))+TIMEVALUE(SUBSTITUTE(MID(D2,FIND(" ",D2,1)+1,8),".",":"))-DATEVALUE(LEFT(C2,FIND(" ",C2,1)))+TIMEVALUE(SUBSTITUTE(MID(C2,FIND(" ",C2,1)+1,8),".",":"))
copied down.
You will need to format it as time
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
Hi,
The problem you have is that the two cells are text not Date/Time numbers hence you can't perform any arithmetic with them until you use some additional functions to first convert them to date/time numbers.
With text 2-Jun-15 03.26.07.565000000 PM I can see the date, but what is the time. Is this 26 minutes 7.565 seconds after 3:00 p.m.?
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.
If my assumption in #3 is correct then perhaps with helper columns
F2:
=DATEVALUE(LEFT(C2,FIND(" ",C2)))+IF(RIGHT(C2,2)="PM",12+MID(C2,FIND(" ",C2)+1,2),MID(C2,FIND(" ",C2)+1,2))/24+MID(C2,FIND(" ",C2)+4,2)/1440+MID(C2,FIND(" ",C2)+7,13)/86400
G2:
=DATEVALUE(LEFT(D2,FIND(" ",D2)))+IF(RIGHT(D2,2)="PM",12+MID(D2,FIND(" ",D2)+1,2),MID(D2,FIND(" ",D2)+1,2))/24+MID(D2,FIND(" ",D2)+4,2)/1440+MID(D2,FIND(" ",D2)+7,13)/86400
H2: formatted as h:m:s.000
=G2-F2
I2: formatted as a number
=H2*86400
This got me what I needed... I needed the delta - ie the time between the two in seconds - so I didn't need the I2 value... Thanks much..Originally Posted by Richard Buttrey
One wierd thing to note, when I copied the data for G2 - it came up a "TRUE" in the cell even though it was formatted the same way as F2... The G2/F2 code looks the same to me - so I don't know why... I just used the F2 code and manually changed the cell info from c2 to D2 and it works great...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks