Update Dates

L

Les

I have 2 cells next to each other called nextyearfromdate and nextyeartodate. The cells are linked to calendars (Calendar Control 9.0).
In another cell a formula calculates the number of months in the date period which fall on a Thursdays between the 15th and 21st only of the month:-

=IF(COUNT(nextyearfromdate:nextyeartodate)=2,SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(nextyearfromdate & ":" & nextyeartodate)))=5),--(DAY(ROW(INDIRECT(nextyearfromdate & ":" & nextyeartodate)))>=15),--(DAY(ROW(INDIRECT(nextyearfromdate & ":" & nextyeartodate)))<=21)),"enter date")

This formula works fine when you type the dates in the cells, but when a date/dates are chosen from the calendar/s you need to click in the formula bar then press Enter to get the formula to work.

Can anyone tell me how to get this to work automatically?
 
S

sebastienm

The problem with the calendar control is that it returns dates as text. Even though the cell is still formatted as date, if you make the cell wider, you'll notice that the calendar linked cell aligns the value to the left (as text) instead of to the right as with numeric/date values.
A workarround:
- say your linked cells are A2 and B2 (NextYearFromDate, NextYearToDate).
- in A1: =datevalue(A2)
- in B1: = datevalue(B2)
- name A1 and B1, NextYearFromDate and NextYearToDate, instead of A2 and B2.
(or give new names and have your formula work on these new names instead)
- hide row 1

Regards,
Sebastien
 
Top