Formula than understands midnight?

A

Auric__

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.
 
C

Claus Busch

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.)
Click to expand...

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.
 
G

GS

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
 
G

GS

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
 
C

Claus Busch

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?
Click to expand...

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.
 
C

Claus Busch

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))
Click to expand...

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

Regards
Claus B.
 
G

GS

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
 
C

Claus Busch

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
Click to expand...

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 here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
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.
 
G

GS

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


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 here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
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.
Click to expand...

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
 
A

Auric__

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


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

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.)
 
G

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.

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
 
C

Claus Busch

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.
Click to expand...

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),NextDate<>""),H3=""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),$H$3:H3))

Regards
Claus B.
 
C

Claus Busch

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:
Click to expand...

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.
 
A

Auric__

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


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
Click to expand...

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.
Click to expand...

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.
 
C

Claus Busch

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
Click to expand...

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.
 
C

Claus Busch

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))
Click to expand...

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


Regards
Claus B.
 
C

Claus Busch

Hi,

Am Fri, 25 Apr 2014 09:17:39 +0200 schrieb Claus Busch:
=IF(MIN(IF(B1:G1>0.5,B1:G1))=0,MAX(B1:G1)-MIN(B1:G1),MOD(MAX(IF(B1:G1<0.5,B1:G1))-MIN(IF(B1:G1>0.5,B1:G1)),1))
Click to expand...

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:G1>0.5,B1:G1)),1),MAX(B1:G1)-MIN(B1:G1))
and enter also with CTRL+Shift+Enter


Regards
Claus B.
 
C

Claus Busch

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)
Click to expand...

if that sub-item.item is in column E then insert that additional
argument into the formula:
=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDate<>""),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.
 
G

GS

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


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),NextDate<>""),H3=""),"",SUMPRODUCT(--(MONTH(C$3:C3)=MONTH(C3)),--($D$3:D3=D3),$H$3:H3))
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
How do I set up a formula to give me an average based on IF? 1
Break Calculation 1
SUMIF with OFFSET 5
Next-To-Last, Non-Blank, Rightmost Value in a Row 6
Conditional Average Array with Dates, <blanks>, 0 7
Compare Rows and delete duplicate records 3
Rank and Sort 4

Top