Need some help with IF statements

[MrCrimmy]

Hello All,

I'm having trouble with some IF statements, effectively what I need to do is report peoples time logged in the office. So from 20th of October people should have logged 39 hours, the next week people should have logged 78 hours and so on. Lets say this information is held in cell C54. That's the easy part, I have this formula for that:

=IF(TODAY()>DATE(2014,10,27),(($C$54/B58)*100),A5)

The data is displayed as follows:

[TABLE="width: 422"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Hours To Date[/TD]
[TD]Compliance[/TD]
[/TR]
[TR]
[TD]20Oct14[/TD]
[TD]39[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]27Oct14[/TD]
[TD]78[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]03Nov14[/TD]
[TD]117[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]10Nov14[/TD]
[TD]156[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17Nov14[/TD]
[TD]195[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]24Nov14[/TD]
[TD]234[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]01Dec14[/TD]
[TD]273[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]
What I want to do is report the most up to date (based on date of start of the week) percentage to a separate sheet called meeting, so say when it passed the 27th of october it will no longer report the information for the previous week, code example:

=IF(TODAY()>DATE(2014,10,20),Meeting!N40 = CumulativeData!C57, IF(TODAY()>DATE(2014,10,27),Meeting!N40 = CumulativeData!C58, IF(TODAY()>DATE(2014,10,27),Meeting!N40 = CumulativeData!C59,......

I just can't get this to work no matter how many iterations i try

Can anyone help me with this? I hope I have made this clear hahah!

Kind Regards,

Bryan

 

[West Man]

It appears that you are trying to populate another cell (Meeting!N40) based on an if condition. Excel formulas can not do that. Your formula must be in cell N40 to get a result in that cell.

 

[West Man]

I think I may understand what you are trying to do. Does this in cell N40 work? =OFFSET(CumulativeData!C56,INT((TODAY()-(DATE(2014,10,20)))/7),0) Working with offset will prevent the need for changing the formula as additional weeks are added

 

[MrCrimmy]

You sir are both a gentleman and a scholar!

Thank you very much!

 

[West Man]

Glad to have helped. If the last number in c57:c1000 would give you the same result, you might try =LOOKUP(9.99999999999999E+307,C57:C1000)