dynamically tally the daily sub total

Hi,

First post so; "hello". I have been browsing your website/forum for many hours now because I found a few *almost* solutions to my problem on a google search. And it seems like this is a really active and helpful community so I thought I would try my luck with my specific problem. I wish I knew what to search for because I searched through your forums for a few keywords and nothing seemed to really fit. I apologies if someone has asked this before. My skill level in excel is fairly basic but I've managed to wrangle together some basic functionality using examples I can find on the web.

-----------------------------------

I'm just putting together a simple time book for my techs to keep track of what jobs they've done each day and how many hours are recorded on each job (or session on each job - Sometimes a job can be done in 1 session, sometimes it takes multiple sessions over multiple days). Each Job or session on a job is 1 Row. We were doing this in a hand written exercise book but I want to be able to record it electronically.

I've created a spreadsheet that will let me enter all the data into columns to be printed out and got the formulas, etc working to tally the start and stop hours entered in 24 hour format (column E and F) into decimal hours (Column G). I then added VBA to it which allows me to automatically create a new sheet for each fortnight when the button is clicked at the end of the fortnight + a few other formatting tricks. That side all seems to work.

The basic functionality is there, but the last hurdle I'm looking to get over is that at the end of each day I want it to tally up the daily sub total so its obvious each day how long they've worked. Its not a payroll system, but its a check to make sure that nothing is missed from payroll.

I have attached a week of sample data to show what I'm trying to do. Essentially I'm trying to tally the daily total in Column H (which I have highlighted yellow) dynamically, but because there may be 1 to say 20 jobs in a day, I cant work out a formula that can sum up a range of integers together based on how many rows are in a certain day.

Because each Tech does something different each day, 1 day he might only work on 1 job for "x" hours, there may be only 1 line, other days there might be 10 lines of jobs they have done which will add up to "y". Example is Row 12 vs the total of Row 13 to Row 18.

To top it off, I want the total to be tallied in Column H on the row above the following day (which is designated by an entry in Column A. If column A is blank, it is the same day as above. I want it to recognise that a Row with some text in Column A is the first entry in the array, and that the row above the next row that has some text in column A is the last entry in the array.


from reading and research, I suspect the final formula is going to need something along the lines of Lookup(), sumif(), index() or the likes. But I am stuffed if I can get anything to work.

I'm really stumped and I've been working on this problem for days all up. There may be a simple solution to this, but because I don't know the terminology, I haven't been able to search it thus far.

On a side note, is there a way that I can get the data being entered into column E & F to auto populate/concatenate the ":00" if tab is pressed for easier data entry? ie if I type "8" then hit TAB, it will set it to 8:00? currently if I type "8" it resets to 0:00. I'm not worried if there this doesn't get answered here because I think this might be a VBA question to go somewhere else in the forum.

Anyway, thank you for any assistance/guidance you might be able to provide me.

Kind Regards,

Lachlan.