First, apologies for the delayed response. I hope this is still useful.
If your primary goal is to import to Quickbooks, then there is probably a better layout than than the "Weekly" worksheet that I have provided?
Again, as you intend to import to Quickbooks then I'm guessing that you want the results in terms of hours and fractions and so, per JetMC's comment I have removed the "/24"s from the formulas in the attached workbook.
The two remaining issues were to deal with midnight crossings and to recognise that days begin at 7:00 am and that time worked between midnight and 7:00 am belongs to the previosu day. That (as best as I can come up with) adds quite a bit of complexity - Maybe someone else has a much cleaner approach!
Here's some details relative to the attached workbook:
To implement the 7:00 am day start:
In Weekly!B1 (the blue shaded cell) enter the the END date AND time of the two week period. For example: 27-Sep 7:00 am. The 7:00 am portion of the date/time is your day begin time.
A number of "helper" columns are needed on the "hours" worksheet to the right of your imported data (the helper columns can be located elsewhere if necessary).
In H2 and copied down:
This formula moves the clock in date/time back by 7 hours so that, for example, time worked between midnight and 7:00 am is counted towards the previous day.
In I2 and copied down:
This formula is the same 7 hour adjustment as for the "In" date/time plus "carry over" hours from the previous day. The max() is just to avoid negative times (which Excel doesn't deal with) for empty rows.
In J2 and copied down:
This formula is just time worked expressed as a fraction of 1 day (24 hours)
In K2 and copied down:
This formula is just time worked expressed in hours
In L2 and copied down:
This formula determines if the clock out date is greater than the clock in date. If so then some or all of the time belongs to tomorrow, reducing the "Today Hours" time in this column.
In M2 and copied down:
This formula is the hours to be carried over to tomorrow (in col-I)
The formulas on the "Weekly" worksheet are the same as before except that they point to the adjusted hours in col-L rather than directly to "Elapsed Hours" in col-F. Also - important - cell B1 needs to be a date/time with the time part = 7:00 am.
Hopefully there is something you can work with here. Let us know.
The attached workbook implements the above.
Bookmarks