Formula HELP !!

[Guest]

Hi Need Help with a complicated formula, dont knoe if its possible or not,
here it goes, I have the following cells laid out.

NAME A B C D E
F G
Above are all dates of beginning of weeks
Bill X x x
Ady x
Tom x

I need a formula that will return data of arrears paymeny e.g in weeks
(1week) (2weeks) and so on, so Ady and Tom would return data 2 weeks, is this
possible taking the current date for a reference?

Any Help???

Thanks a lot

 

[Guest]

Hello,

Dates (by themselves) are just numbers (to Excel). In their naked form
without formatting, you see the date 'number'. When you want to display them,
use something like Format(someValue, "dd-mm-yyyy"). (You can look up
specifically desired formats on the FormatCells menu option).

Think what you might want below (just guessing), have something like the
following

Public startDate as Date ' make this a global variable

in the Workbook_Open event code, say something like startDate = "1 Jan 2006"
(or whatever starting date you like)

then write your function to do :

Public Function myOffset(InputCell as Range)
Dim difference as Integer
difference = startDate - InputCell
myOffset = difference / 7

End Function

Then have your cell = myOffset(B2) & " weeks"

Hope this helps,
Chad

 

[Guest]

Hi, where do I enter this formula, can you explain, I may seem a bit dumb but
i'm a beginner at this.


Santaviga

 

[Guest]

No Problem

1) Press Alt + F11 (to get to the VBA editor)

You'll see an outline of all the worksheets in your project.

2) Double click on ThisWorkbook ...

You'll see a code window open up, click on the pulldown for Events and
click on Workbook_Open

in the body of the sub, just put the statement
startDate = "1 Jan 2006" (or whatever start date you want)

3) then insert a module in the window. (for inserting new code)

Public startDate as Date

Public Function myOffset(InputCell as Range)
Dim difference as Integer
difference = startDate - InputCell
myOffset = difference / 7

End Function

Then. at the Cell you want to associate with the function call,
click on the cell and press =myOffset(B2) where B2 is the cell you want
to modify.

Chad

 

[Guest]

What value do I input in (InputCell as Range) ???

Ta

 

[Guest]

also when inputting startDate = "1 Jan 2006" I am getting error compile error.

Mark

 

[Guest]

It will be a compile error until you insert a module ...
Then at the top of the module, put in the statements:

Public startDate as Date ' This will make startDate a global variable
Public Function myOffset(TargetCell as Range)

myOffset = TargetCell.Value - startDate

End Function

(try cutting and pasting this code to the module...)

then, go back to the spreadsheet and click on any cell.

hit the '=' sign, and type in: myOffset(
Then left-click the cell you want to reference (it should contain a date
value)
then press ')' & " weeks"

So, the formula for the cell in question should look something
like...

= myOffset(B2) & " weeks"

hope this helps,
Chad

 

[Guest]

Hi there. I'm thinking that you want a formula for your worksheet rather
than something in VBA. Is that correct? I need to know what you have
listed above each column for column A - G.

 

[Guest]

Sorry, meant to write myOffset = (TargetCell.Value - startDate) / 7

 

[Guest]

I have dates e.g 1/1/06 8/1/6 15/1/6 and so on to the end of the worksheet
and need a formula to report arrears in weeks to a cell next to a name in
relation to a X being put in a cell relating to the week saying that that
week has been paid..

Thanks

 

[Guest]

Let's say that your dates are in column 1.

Let's also say that Bill's information is in column 3 (specifically cells
B3:H3)

Try this formula:

=INT(TODAY()-INDEX($1:$1,1,MATCH("x",A3:H3)))/7

 

[Guest]

Hi, Sorry this is returning a date and time in 1900 I need it to return data
1 WEEK or 2 WEEK or 3 WEEK depening on the x's in the information according
to week commencing dates.

Mark

 

[Guest]

To the end of the WORKSHEET? That's like 256 columns. Do you really have
that much data?

 

[Guest]

Format it as NUMBER.

Hi, Sorry this is returning a date and time in 1900 I need it to return data
1 WEEK or 2 WEEK or 3 WEEK depening on the x's in the information according
to week commencing dates.

Mark

 

[Guest]

Hi tried all the info you sent but the cell returns a value error any ideas??

Mark

 

[Guest]

Thanks a lot Barb, Great stuff.


Regards,

Mark

Format it as NUMBER.