In my logs, I have a series of times entered into columns B through G, then
in H there is this formula:

=MAX(B1:G1)-MIN(B1:G1)

....which calculates the total time for the line. Simple, no?

No. Most days, there is an entry that spans midnight:

B C D E F G H
23:58 0:06 0:19 0:25 (blank) (blank) 23:52
23:48 23:54 (blank) (blank) 23:58 0:05 23:53
(blank)(blank) 23:35 0:04 (blank) (blank) 23:31

In case it's not obvious, H is wrong. (Should be 0:27, 0:17, 0:29.)

Right now, I simply manually enter one of these when an entry spans
midnight:

=1+(E1-B1)

....replacing B & E with whatever is appropriate (although those are the most
frequent).

I've tried a few different formulae to get this to happen automagically, but
none of them really work consistently, and I'm not really happy with any of
them. Most importantly: I don't know how to figure out which column is the
earliest time (i.e. 23:48) and which is the latest (i.e. 0:05) without
resorting to VBA, which I feel certain shouldn't be necessary for this.

Does anyone have a good solution for this?

If it matters...
- E and G are mutually exclusive; I won't have both on the same line.
- If F is non-blank, it will *always* be earlier than D, E, and G, and
*always* later than B (and B will *always* be non-blank). Could be
either way with C.

--
The way to handle back-seat drivers is to remove the back seat.

Hi Auric,

Am Thu, 24 Apr 2014 05:33:24 +0000 (UTC) schrieb Auric__:

B C D E F G H
23:58 0:06 0:19 0:25 (blank) (blank) 23:52
23:48 23:54 (blank) (blank) 23:58 0:05 23:53
(blank)(blank) 23:35 0:04 (blank) (blank) 23:31

In case it's not obvious, H is wrong. (Should be 0:27, 0:17, 0:29.)

you have to calculate each working time separatly.
Try in H1:
=MOD(C1-B1,1)+MOD(E1-D1,1)+MOD(G1-F1,1)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Not sure what you're doing exactly, but you can check out how I record
time spent on projects here...

https://app.box.com/s/23yqum8auvzx17h04u4f

...where each session has a 'start' and 'stop' time on a separate row.
The sheet also includes cumulative time totals for month,project and
lets you specify an hourly rate for billing followed by cumulative
amount totals for the month,project.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Claus,
Please check out the link I posted to Auric. Look for the file named
"ProjectsTimeRecord.xls".

I simplified my 'ElapsedTime' formula using your formula for evaluating
past midnight. Do you have any recommendations for a simpler formula
for the 'Month' column of the 'Totals' section?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Thu, 24 Apr 2014 12:54:38 -0400 schrieb GS:

I simplified my 'ElapsedTime' formula using your formula for evaluating
past midnight. Do you have any recommendations for a simpler formula
for the 'Month' column of the 'Totals' section?

I first had to study your range names ;-)

In I3 try:
=IF(AND(MONTH(ThisDate)=MONTH(NextDate),NextDate< ""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),$H$3:H3))
and copy down


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Thu, 24 Apr 2014 20:39:39 +0200 schrieb Claus Busch:

=IF(AND(MONTH(ThisDate)=MONTH(NextDate),NextDate< ""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),$H$3:H3))

better:
=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDat e<""),F3=""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),$H$3:H3))

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thank you, Claus! Much shorter...

This works fine in the 1st section, but doesn't work in the 2nd section
(as I'm sure you figured out) or beyond without revising the starting
row for the section! I have had to make that change for each section
added. This works fine once that change is made. This formula, however,
requires many more changes (9 places) than my original formula does (3
places only), but I can use Find/Replace on a selected range once the
formula is copied to a new section.

I'm not familiar with using SUMPRODUCT() and so the lengthy formula I
was using is how I went because it self-explains the logic of the calc.
Using SUMPRODUCT() doesn't provide the same (IMO) to the casual user of
this template.

I think you are a formula wizard! Your postings have often left me awe
struck, to say the least, and this suggestion fits that way too! Much
appreciated!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Thu, 24 Apr 2014 16:33:27 -0400 schrieb GS:

This works fine in the 1st section, but doesn't work in the 2nd section

my formula gives me the same results as yours. I started in I3 and
copied down (also over the dotted rows wiht "Copy without formats")
Please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for the workbook "ProjectsTimeRecord".
My formula is in the column for the comments.
If that is not what you want, can you please explain me in words which
output is expected.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Thu, 24 Apr 2014 16:33:27 -0400 schrieb GS:

This works fine in the 1st section, but doesn't work in the 2nd
section

my formula gives me the same results as yours. I started in I3 and
copied down (also over the dotted rows wiht "Copy without formats")
Please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for the workbook "ProjectsTimeRecord".
My formula is in the column for the comments.
If that is not what you want, can you please explain me in words
which output is expected.


Regards
Claus B.

Aha! I see that. Brilliant.., and is why you are the formula wizard!! I
will have to apply this to some existing project sheets to make sure my
CFs persist after the copy down. Otherwise, this makes using this
template all that much easier...! Big thanks!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I will have to apply this to some existing project sheets to make
sure my CFs persist after the copy down.

Excellent!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Thu, 24 Apr 2014 17:10:31 -0400 schrieb GS:

Excellent!!!

I am glad, that I could help you. Appreciate your feedback.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch wrote:

Am Thu, 24 Apr 2014 05:33:24 +0000 (UTC) schrieb Auric__:

B C D E F G H
23:58 0:06 0:19 0:25 (blank) (blank) 23:52
23:48 23:54 (blank) (blank) 23:58 0:05 23:53
(blank)(blank) 23:35 0:04 (blank) (blank) 23:31

In case it's not obvious, H is wrong. (Should be 0:27, 0:17, 0:29.)

you have to calculate each working time separatly.
Try in H1:
=MOD(C1-B1,1)+MOD(E1-D1,1)+MOD(G1-F1,1)

Okay, thanks for that, but...

I see now that my previous examples make it look like the times are in
groups of 2, but they're not. Any of the times might be blank. Here's a
selection from my log showing several possibilities:

B C D E F G H
21:43 21:52 21:52 0:09
22:01 22:12 22:15 22:20 22:14 0:19
22:57 23:09 23:13 0:16
23:15 23:28 0:13
1:15 1:19 1:23 1:34 0:19
2:50 3:00 3:03 3:05 0:15

(The only thing truly consistent is that if E has an entry, G will be blank,
and vice-versa.)

I tried some variations on your formula, but nothing I came up with works
across the board. I tried checking for blanks and ignoring them, but I can't
quite get that right. (For example, in the line starting with 22:57, I get a
time of 0:04, rather than the correct answer as listed in H, because I don't
know how to check for D-B in that case.)

--
You supply the rumours and I'll provide the wrath.

Ok.., the only issue I have found thus far is that this formula doesn't
work if I overwrite ElapsedTime with a constant value in place of
entering start/stop times. My purpose for this is to permit using 'flat
rate' for time as opposed to 'straight time' spent.

Just for clarity...
The design intent of this template is to permit:
1 sheet per client, multiple individual projects and/or sub-projects
OR
1 sheet per project, multiple individual sub-projects

...and so each section needs to be stand-alone for calcs. This permits
any number of sub-levels...

Project1 (main item)
Project1-Part1 (sub-item)
Project1-Part2 (sub-item)
Project1-Part2.1 (sub-item.item)
Project1-Part2.2 (sub-item.item)

Project2 (main item)
Project2-Part1 (sub-item)
Project2-Part2 (sub-item)
Project2-Part2.1 (sub-item.item)
Project2-Part2.2 (sub-item.item)

...and so on!

I have yet to compare with prior project times whether this new formula
meets the above criteria...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Thu, 24 Apr 2014 21:01:20 -0400 schrieb GS:

Ok.., the only issue I have found thus far is that this formula doesn't
work if I overwrite ElapsedTime with a constant value in place of
entering start/stop times. My purpose for this is to permit using 'flat
rate' for time as opposed to 'straight time' spent.

that is because of the IF statement at start. If Start time = "" then
output = "". Change the start time to the elapsed time that means change
F3 to H3:
=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDat e<""),H3=""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),$H$3:H3))

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Auric,

Am Fri, 25 Apr 2014 00:27:10 +0000 (UTC) schrieb Auric__:

I see now that my previous examples make it look like the times are in
groups of 2, but they're not. Any of the times might be blank. Here's a
selection from my log showing several possibilities:

if your log data is in correct order (first login to last login) you
could try it with:
=MOD(INDEX(B1:G1,MATCH(0,B1:G1,-1))-INDEX(B1:G1,MATCH(TRUE,B1:G1<"",0)),1)
This is an array formula to enter with CTRL+Shift+Enter

I am still thinking for a solution if the times are not in order.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch wrote:

Am Fri, 25 Apr 2014 00:27:10 +0000 (UTC) schrieb Auric__:

I see now that my previous examples make it look like the times are in
groups of 2, but they're not. Any of the times might be blank. Here's a
selection from my log showing several possibilities:

if your log data is in correct order (first login to last login) you
could try it with:
=MOD(INDEX(B1:G1,MATCH(0,B1:G1,-1))-INDEX(B1:G1,MATCH(TRUE,B1:G1<"",0)),
1)
This is an array formula to enter with CTRL+Shift+Enter

Wow. Thank you. Certainly beyond what I would've thought to try.

I am still thinking for a solution if the times are not in order.

It's rare, but yes, there are entries where I have something like this:

22:01 22:12 22:15 22:20 22:14

In this case, your formula returns 0:13, while the actual value should be
0:19.

I'm wondering if maybe I should just add some conditional formatting to the
problem column (H) to highlight anything over, say, an hour. While it isn't
what I want, it would make problems stand out visually, for immediate manal
editing.

--
It's so adorable when you nerd rage!

Hi Auric,

Am Fri, 25 Apr 2014 06:41:14 +0000 (UTC) schrieb Auric__:

It's rare, but yes, there are entries where I have something like this:

22:01 22:12 22:15 22:20 22:14

please test following array formula if it is working with all your data
and give me a feedback:
=IF(INDEX(B1:G1,MATCH(TRUE,B1:G1<"",0))-INDEX(B1:G1,MATCH(0,B1:G1,-1))0.5,MOD(INDEX(B1:G1,MATCH(0,B1:G1,-1))-INDEX(B1:G1,MATCH(TRUE,B1:G1<"",0)),1),MAX(B1:G1)-MIN(B1:G1))



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Fri, 25 Apr 2014 09:02:00 +0200 schrieb Claus Busch:

=IF(INDEX(B1:G1,MATCH(TRUE,B1:G1<"",0))-INDEX(B1:G1,MATCH(0,B1:G1,-1))0.5,MOD(INDEX(B1:G1,MATCH(0,B1:G1,-1))-INDEX(B1:G1,MATCH(TRUE,B1:G1<"",0)),1),MAX(B1:G1)-MIN(B1:G1))

another suggestion:
=IF(MIN(IF(B1:G10.5,B1:G1))=0,MAX(B1:G1)-MIN(B1:G1),MOD(MAX(IF(B1:G1<0.5,B1:G1))-MIN(IF(B1:G10.5,B1:G1)),1))
to enter with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi,

Am Fri, 25 Apr 2014 09:17:39 +0200 schrieb Claus Busch:

=IF(MIN(IF(B1:G10.5,B1:G1))=0,MAX(B1:G1)-MIN(B1:G1),MOD(MAX(IF(B1:G1<0.5,B1:G1))-MIN(IF(B1:G10.5,B1:G1)),1))

the last posted formula is wrong.
Better try:
=IF(MAX(B1:G1)-MIN(B1:G1)0.5,MOD(MAX(IF(B1:G1<0.5,B1:G1))-MIN(IF(B1:G10.5,B1:G1)),1),MAX(B1:G1)-MIN(B1:G1))
and enter also with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Thu, 24 Apr 2014 21:01:20 -0400 schrieb GS:

Project1 (main item)
Project1-Part1 (sub-item)
Project1-Part2 (sub-item)
Project1-Part2.1 (sub-item.item)
Project1-Part2.2 (sub-item.item)

Project2 (main item)
Project2-Part1 (sub-item)
Project2-Part2 (sub-item)
Project2-Part2.1 (sub-item.item)
Project2-Part2.2 (sub-item.item)

if that sub-item.item is in column E then insert that additional
argument into the formula:
=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDat e<""),H3=""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),--($E$3:E3=E3),$H$3:H3))
or send me a workbook with both versions and the expected result.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Thu, 24 Apr 2014 21:01:20 -0400 schrieb GS:

Ok.., the only issue I have found thus far is that this formula
doesn't work if I overwrite ElapsedTime with a constant value in
place of entering start/stop times. My purpose for this is to
permit using 'flat rate' for time as opposed to 'straight time'
spent.

that is because of the IF statement at start. If Start time = "" then
output = "". Change the start time to the elapsed time that means
change F3 to H3:
=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDat e<""),H3=""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),$H$3:H3))

I already figured that out! It works as expected when the ref is
ElapsedTime instead of Start! Thanks for confirming...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Thu, 24 Apr 2014 21:01:20 -0400 schrieb GS:

Project1 (main item)
Project1-Part1 (sub-item)
Project1-Part2 (sub-item)
Project1-Part2.1 (sub-item.item)
Project1-Part2.2 (sub-item.item)

Project2 (main item)
Project2-Part1 (sub-item)
Project2-Part2 (sub-item)
Project2-Part2.1 (sub-item.item)
Project2-Part2.2 (sub-item.item)

if that sub-item.item is in column E then insert that additional
argument into the formula:
=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDat e<""),H3=""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),--($E$3:E3=E3),$H$3:H3))
or send me a workbook with both versions and the expected result.

There is no change in how times are entered for any items. Where/how I
calc billing depends on the nature of the project I'm tracking time
for.

(The most important thing is to be able to use straight time, flat
rate, or both as needed!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Claus,
I have revised the new formula as follows...

=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDat e<""),This_ET=""),"",SUMPRODUCT(--(MONTH(B$3:ThisDate)=MONTH(ThisDate)),--($D$3:ThisProject=ThisProject),$H$3:This_ET))

...as well as updated the CF to use defined names instead of cell
addresses.

A new version has been uploaded and so the link posted earlier will
make the 'ProjectsTimeRecord.xls' template available to anyone who's
interested...

Thanks so much for helping me, and for contributing to my better
understanding/familiarity with SUMPRODUCT()! Much appreciated...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Fri, 25 Apr 2014 12:33:56 -0400 schrieb GS:

=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDat e<""),This_ET=""),"",SUMPRODUCT(--(MONTH(B$3:ThisDate)=MONTH(ThisDate)),--($D$3:ThisProject=ThisProject),$H$3:This_ET))

..as well as updated the CF to use defined names instead of cell
addresses.

thank you for the information. I will look for the new version.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Fri, 25 Apr 2014 12:33:56 -0400 schrieb GS:

=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDat e<""),This_ET=""),"",SUMPRODUCT(--(MONTH(B$3:ThisDate)=MONTH(ThisDate)),--($D$3:ThisProject=ThisProject),$H$3:This_ET))

..as well as updated the CF to use defined names instead of cell
addresses.

thank you for the information. I will look for the new version.


Regards
Claus B.

For some reason the 'Upload new version' action didn't/doesn't work and
so I just deleted/re-uploaded since your post...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Fri, 25 Apr 2014 12:52:29 -0400 schrieb GS:

For some reason the 'Upload new version' action didn't/doesn't work and
so I just deleted/re-uploaded since your post...

I already downloaded the new version without any problems.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch wrote:

Am Fri, 25 Apr 2014 09:17:39 +0200 schrieb Claus Busch:

=IF(MIN(IF(B1:G10.5,B1:G1))=0,MAX(B1:G1)-MIN(B1:G1),MOD(MAX(IF(B1:G1<0.
5,B1:G1))-MIN(IF(B1:G10.5,B1:G1)),1))

the last posted formula is wrong.
Better try:
=IF(MAX(B1:G1)-MIN(B1:G1)0.5,MOD(MAX(IF(B1:G1<0.5,B1:G1))-MIN(IF(B1:G10
.5,B1:G1)),1),MAX(B1:G1)-MIN(B1:G1)) and enter also with
CTRL+Shift+Enter

Amazing. Works like a charm. Thanks for your hard work.

--
Mom used to tuck me into bed at night.
She'd kiss me on the forehead and say, "Nighty-night."
Then Dad would tiptoe in and whisper, "Beware the chicken!"