Any Excel experts?

  montyburns 20:03 21 Mar 2006
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 2006

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 2006

No need for extra column:

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

  montyburns 00:01 22 Mar 2006

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 2006

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.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?