Formula than understands midnight?

  • Thread starter Thread starter Auric__
  • Start date Start date
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$3:D3=D3),--($E$3:E3=E3),$H$3:H3))
or send me a workbook with both versions and the expected result.
Click to expand...

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

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


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

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

I already downloaded the new version without any problems.


Regards
Claus B.
 
Claus said:
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
Click to expand...

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