Dazza40 20:41 02 Jan 2007
Locked

Hi,
I'm looking to usea formula to help me with the following
Column A
Dates 1/1/07, 2/1/07 and so on
Column B
1, 1, 2, 3, 4, and so on were the weekdays increase by one. Weekends do not increase the number.
I have set thsi up, what I would like is a formula that in Column c row 1 shows the number that is associated with TODAY(). This will help me show how many selling days have been used according to which daye in the month. This number of days would then be used in formulas elsewhere in the spreadsheet.

Any ideas would be welcome. I have looked at LOOKUP and VLOOKUP but can't get it to work.

Cheers

Darren

VoG II 20:53 02 Jan 2007

TODAY() returns today's date. Do you mean that you want to use =WEEKDAY(TODAY())

Even if you do I'm still not clear what you want to achieve.

Dazza40 21:18 02 Jan 2007

Sorry VoG™.
What I would like to do is use a formula that matches today with the number alongside that date. e.g TODAY being the 1/1 would show 1. If TODAY was the 2/1 then this the result would be 1, if 3/1 then result would be 2 and so on.

The result 1,2 etc would then be used elsewhere. It is used for sales targetting. In Jan there are 22 selling days igniring Bank Hol Monday and weekends.

Does this make sense?

VoG II 21:37 02 Jan 2007

"TODAY being the 1/1 would show 1. If TODAY was the 2/1 then this the result would be 1, if 3/1 then result would be 2 and so on."

But where are we supposed to get this information from?

Are you asking that if the WEEKDAY is the same as today's day (Tuesday) then sum those numbers from column B? I am not sure what column C contains.

Dazza40 21:45 02 Jan 2007

Sorry, does this help?
Column C does not have anything in. I would like the result to show there in say C1.
Column A has the dates listed down the rows.
Column B has the appopriate numbers already keyed in.

I would like to match todays date (whatever that is) in Column A with the number next to it in Column B.

If today was the 3/1 then A3 would be 3/1 and B3 would be 2. I would like the result to be 2. If the date was the 4/1 then A4 would be the 4/1 and B4 is 3 next to it.

I'm looking to match todays date and give a number.
I could have TODAY() say as cell C1 with the result showing is say cell D1. Where the reult shows is not really an issue as I will be using this number elsewhere.

Hope this makes more sense.

VoG II 22:03 02 Jan 2007

Try

=VLOOKUP(TODAY(), A1:B100, 2, FALSE)

as as start. This will only find thefirst entry for the second of January and I still do not understand what you want to achieve.

Dazza40 22:15 02 Jan 2007

Hi VoG™,
Sorry didn't work. I have managed a long winded way of getting the result. I will mark as resolved aa this is how I did it. If you can think of a smater way i would be very grateful.
A1 has the date 1/1/07
b1 has the numbers in this case 1
c1 has an IF formula where if A1=TODAY() answer is 1 (or 0 if not)
D1 has another If formula where if C1=1 show B1 if not 0.
I have continued this down so that in column D I will get either 0 or a number from column B. I then use Sum to total these numbers up.

It works but as I said is long winded....

Thanks for your help, guess if I could explain better it would have been easier.

Cheers

Darren

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

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

HomePod review

Comment regarder les Oscars 2018 ?