Formula than understands midnight?

[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$33=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

 

[GS]

Hi Garry,

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


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$33=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

 

[GS]

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

=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDate<>""),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

 

[Claus Busch]

Hi Garry,

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

=IF(OR(AND(MONTH(ThisDate)=MONTH(NextDate),NextDate<>""),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.

 

[GS]

Hi Garry,

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


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

 

[Claus Busch]

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.

 

[Auric__]

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


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

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