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

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

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.

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

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