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

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


"santaviga" wrote:

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

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


Santaviga

"ChadF" wrote:

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


"santaviga" wrote:

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

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


"santaviga" wrote:

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


Santaviga

"ChadF" wrote:

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


"santaviga" wrote:

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

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

Ta

"ChadF" wrote:

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


"santaviga" wrote:

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


Santaviga

"ChadF" wrote:

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


"santaviga" wrote:

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

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

Mark

"ChadF" wrote:

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


"santaviga" wrote:

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


Santaviga

"ChadF" wrote:

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


"santaviga" wrote:

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

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




"santaviga" wrote:

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

Mark

"ChadF" wrote:

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


"santaviga" wrote:

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


Santaviga

"ChadF" wrote:

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


"santaviga" wrote:

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

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.

"santaviga" wrote:

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

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


"ChadF" wrote:

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




"santaviga" wrote:

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

Mark

"ChadF" wrote:

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


"santaviga" wrote:

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


Santaviga

"ChadF" wrote:

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


"santaviga" wrote:

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

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

"Barb Reinhardt" wrote:

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.

"santaviga" wrote:

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

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

"santaviga" wrote:

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

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

"Barb Reinhardt" wrote:

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

"santaviga" wrote:

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

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

"santaviga" wrote:

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

"Barb Reinhardt" wrote:

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.

"santaviga" wrote:

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

Format it as NUMBER.

"santaviga" wrote:

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

"Barb Reinhardt" wrote:

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

"santaviga" wrote:

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

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

Mark

"ChadF" wrote:

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


"ChadF" wrote:

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




"santaviga" wrote:

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

Mark

"ChadF" wrote:

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


"santaviga" wrote:

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


Santaviga

"ChadF" wrote:

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


"santaviga" wrote:

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

Thanks a lot Barb, Great stuff.


Regards,

Mark

"Barb Reinhardt" wrote:

Format it as NUMBER.

"santaviga" wrote:

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

"Barb Reinhardt" wrote:

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

"santaviga" wrote:

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