Any Excel experts?

  montyburns 20:03 21 Mar 2006

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


and copy down as far as needed.

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


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:


  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


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

Honor 9 Lite review

How Social Media has Propelled Political Graphic Design and Art in the Last Decade

The best kids apps for iPhone & iPad 2018

HomePod d’Apple : date de sortie, prix et fiche technique