Any Excel experts?

  montyburns 20:03 21 Mar 06
Locked

I'm stuck on a problem with Excel

I have a staff leave workbook, which lists all the current year's dates in column B, and then the leave in column C (days of week in column A)

As staff book leave, I want to keep a running total of days booked at the foot of column B.

But I'd also like to show how many days have actually been taken.

I've managed to get the current date to highlight using conditional formatting, but can't think of a way to extract days taken between start of leave year and "today"

Any help appreciated!

  VoG II 20:35 21 Mar 06

There may be a more elegant way to do this but:

Create an extra column D and enter the formula

=B1<TODAY()

and copy down as far as needed.

The days actually taken, not including today, is then given by

=SUMIF(D1:D4,TRUE,C1:C4)

you need to substite 4 by the row that your data ends in.

  VoG II 20:47 21 Mar 06

No need for extra column:

=SUMPRODUCT(--(B1:B4<TODAY()),(C1:C4))

  montyburns 00:01 22 Mar 06

I like the look of that! I was trying to play around with SUMPRODUCT today but got nowhere...

I'll give it a go tomorrow and report back - cheers VoG!

  montyburns 07:52 22 Mar 06

Right, I've tried it! Extended the formula so it related to the columns

=SUMPRODUCT(--(B5:B369<TODAY()),(C5:C369))

And it works a treat!!

You're a star VoG! ;-)

VoG = "Voice of God"?

This thread is now locked and can not be replied to.

LG G6 review: Hands-on with LG’s bold, big-screen shot at perfection

1995-2015: How technology has changed the world in 20 years

How the 2017 Oscar-winning VFX of The Jungle Book were created

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…