I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.
Start/Finish
21/12/09 07:00 to 21/12/09 11:09
21/12/09 07:46 to 21/12/09 14:41
21/12/09 12:13 to 21/12/09 22:08
21/12/09 16:40 to 21/12/09 18:05
21/12/09 19:40 to 22/12/09 02:34
21/12/09 23:20 to 22/12/09 04:39
22/12/09 02:06 to 22/12/09 06:15
any ideas?
I am trying to develop a way of solving a day shift/ night shift problem (I attach a sample data dump the actual data to be analysed is hundreds of lines). If I have an exited off-site as the first entry of the day this means that the person worked night shift and I need to calculate the hours on site for that day from midnight. If the first entry of the day is a entry to site then I need to calculate the time between this event and the exited off-site event for each employee. If the employee does not exit off-site then I need to calculate the time from the entry to site until midnight.
The sample data is in the format I can extract from my source system. Please assist me in solving this issue.
Cheers
I have followed the procedure used for constructing a time sheet (Learn Excel 2007 Fromm Mr Excel) - BUT this does'nt work with a start time early evening ie. 18:00 and a finish time of 06:00.
Please can anybody help?
I have cells that are custom formatted to Ddd Mmm D h:mm:ss Yyyy
and they read Fri Apr 1 09:36:42 2011
there are two columns, one with start (A) and one with end (B)
I NEED TO CREATE A COLUMNS THAT CALCULATES THE ELAPSED TIME ONLY (THE HOURS MINUTES SECONDS)
I HAVE NOT BEEN ABLE TO FIND A FORMULA THAT GIVES ME ONLY THE TIME ELAPSE
I have a CELL that is receiving updating numbers via RTD feed AUTOMATICALLY ie. CELL G1
My Excel 2010 Spreadsheet goes like this:
ie. total volume G1 = 1,333,783 was 1 sec ago
now. total volume G1 = 1,333,883 is right now. A trade on the RTD feed just added 100 stocks.
The real problem is:
I need to check G1 AUTOMATICALLY which is updating AUTOMATICALLY. To compare 'G1 Previous' to 'G1 at the moment' and have a VBA code see if it was added to. (The trick is that G1 has already changed upwards to 1,334,883 by now!
The easy part actually what I want to do is:
To make a single macro fire off just to insert G1 and the whole RANGE 'A1 to G1' to 'I1 to O1' and make the previous 'I1 to O1' to go down 1 row.
The real sticky part is that its RTD so via RTD ADD-IN so they don't like VBA Worksheet Functions: 'calculate' nor 'sheet change'
There is no DDE Windows application running, RTD uses the Excel Add-In infrastructure.
I have been reading the posts here and have made great progress. Getting the correct elapsed time to display will either work for instances under 24 hours or orver, but I can't get both to happen with the same formula.
Currently using this:
=INT(b2-a2) & " days " & TEXT(MOD(b2-a2,1),"[h]:mm:ss")
I cannot change the date system to 1904, so I'm getting a negative number for the days. Additionally, for time that has elapsed that is less then 24 hours, the days value is still -1 (rather then 0). Time appears to be calculating fine. Time elapsed over 24 hours, the days seem to be correct, but a negative value.
Does anyone have a suggestion for getting the days to calculate properly? I have attached a sample of the data I'm using.
Can someone explain to me if its possible to calculate the amount of night time spent traveling from point A to Point B using the location's LAT/LONG coordinates and time?
ex: if I start from Point A (i.e. 1600) and arrive at point B at (i.e. 2200), how to calculate the amount of night time spent traveling at night?
Hello Excel wizards!
Does anyone have a solution to this simple problem:
I have plotted a graph of variable against time domain using a scatter graph (or the line graph). In the time domain (x axis) I have the time formatted as hours where the range is about 96 hours long. I really want to be able to make a easy distinction between the periods I am considering to be "night" and "day". It would be ideal if "night" (say each 22:00 until 07:00) was 'shaded' or something so the at-a-glance viewer can see what the variable is typically at night.
Any ideas /suggestions?
I can calculate shift lengths from start and finish times easily for day shifts, but when a shift starts before midnight and finishes after midnight my formulae dont work
Has anyone come up with a solution for tracking working hours for night workers?
Hi all,
I wanted to make an excel-sheet to keep track of my hours.
I want it to look somewhat like the sheet below. Something very simple with the hour in, out, the break subtracted from the total hours worked, a sub-total and a total at the end. However, I ran into broblems whilst calculating the night-time hours. I found that the following formula (=MOD(C11-B11;1)-(D11)) would help to calculate the subtotal. To only problem now is that I can't calculate the total at the end of the month somehow....
Can someone help me please!
Untitled-1.jpg
