Fiscal Year function

  • Thread starter Thread starter BigLar86
  • Start date Start date
B

BigLar86

Hi new to the forum - hope this is correct format

Half my work deals with the calendar year 1/1-12/21
and half deals with FY 10/1 - 9/30

I am trying to build a function that i could apply to a worksheet that
would search out all cells formated as date and change date to FY date
set.

I am new to VBA but not to excel - this is what i use currently

=IF(MONTH(cell)<=9,YEAR(cell),YEAR(cell)+1

any ideas?

BigLar
 
Where is the resultant value supposed to go, in a cell to the right, below?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Where is the resultant value supposed to go, in a cell to the right, below?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)










- Show quoted text -
Click to expand...

Currently cell to right - best of all worlds would like to insert cell
to right, run, delete first cell so my sheet structure was not
altered.
Lar
 
Public Sub ProcessData()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.Cells

If Month(cell.Value) <= 9 Then

cell.Value = DateSerial(Year(cell.Value) + 1, Month(cell.Value),
Day(cell.Value))
End If
Next cell
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Where is the resultant value supposed to go, in a cell to the right,
below?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)










- Show quoted text -
Click to expand...

Currently cell to right - best of all worlds would like to insert cell
to right, run, delete first cell so my sheet structure was not
altered.
Lar
 
Public Sub ProcessData()
Dim cell As Range

    For Each cell In ActiveSheet.UsedRange.Cells

        If Month(cell.Value) <= 9 Then

            cell.Value = DateSerial(Year(cell.Value) + 1, Month(cell.Value),
Day(cell.Value))
        End If
    Next cell
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)





Currently cell to right - best of all worlds would like to insert cell
to right, run, delete first cell so my sheet structure was not
altered.
Lar- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you! I am getting an 'expected arguement' error - but i am not
asking for clarification. I appreciate that you given me and want to
try and take it apart to learn from.

Thanks :)
 
It is probably that the DateSerial line has got wrapped into two lines in
the post.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Public Sub ProcessData()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.Cells

If Month(cell.Value) <= 9 Then

cell.Value = DateSerial(Year(cell.Value) + 1, Month(cell.Value),
Day(cell.Value))
End If
Next cell
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)





Currently cell to right - best of all worlds would like to insert cell
to right, run, delete first cell so my sheet structure was not
altered.
Lar- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you! I am getting an 'expected arguement' error - but i am not
asking for clarification. I appreciate that you given me and want to
try and take it apart to learn from.

Thanks :)
 
it was the DateSerial line - took me about 45 to figure it out but i
got it.
thank you thank you.
I am working on error handling, for fields with text and blank fields
- if you would be so kind as to point me in the right direction I
would like to give it a try.


It is probably that the DateSerial line has got wrapped into two lines in
the post.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)










Thank you!  I am getting an 'expected arguement' error - but i am not
asking for clarification.  I appreciate that you given me and want to
try and take it apart to learn from.

Thanks :)- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks again, it was the ser
 
I will try, but I need a bit more detail as to what you are alluding to.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



it was the DateSerial line - took me about 45 to figure it out but i
got it.
thank you thank you.
I am working on error handling, for fields with text and blank fields
- if you would be so kind as to point me in the right direction I
would like to give it a try.


It is probably that the DateSerial line has got wrapped into two lines in
the post.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)










Thank you! I am getting an 'expected arguement' error - but i am not
asking for clarification. I appreciate that you given me and want to
try and take it apart to learn from.

Thanks :)- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks again, it was the ser
 
I will try, but I need a bit more detail as to what you are alluding to.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)


it was the DateSerial line - took me about 45 to figure it out but i
got it.
thank you thank you.
I am working on error handling, for fields with text and blank fields
- if you would be so kind as to point me in the right direction I
would like to give it a try.







Thanks again, it was the ser- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry about that - it works great for all cells with date - but i have
a blank cell it formats as date and puts the 1900 date in there. Also
if a cell has text it throws an error of data type mismatch. Any help
would be appreciated.
lar.
 
Public Sub ProcessData()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.Cells

If cell.Value <> "" Then

If Month(cell.Value) <= 9 Then

cell.Value = DateSerial(Year(cell.Value) + 1, _
Month(cell.Value), _
Day(cell.Value))
End If
End If
Next cell
End Sub




--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



I will try, but I need a bit more detail as to what you are alluding to.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)


it was the DateSerial line - took me about 45 to figure it out but i
got it.
thank you thank you.
I am working on error handling, for fields with text and blank fields
- if you would be so kind as to point me in the right direction I
would like to give it a try.







Thanks again, it was the ser- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry about that - it works great for all cells with date - but i have
a blank cell it formats as date and puts the 1900 date in there. Also
if a cell has text it throws an error of data type mismatch. Any help
would be appreciated.
lar.
 
Public Sub ProcessData()
Dim cell As Range

    For Each cell In ActiveSheet.UsedRange.Cells

        If cell.Value <> "" Then

            If Month(cell.Value) <= 9 Then

                cell.Value = DateSerial(Year(cell.Value)+ 1, _
                                        Month(cell.Value), _
                                        Day(cell.Value))
            End If
        End If
    Next cell
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








Sorry about that - it works great for all cells with date - but i have
a blank cell it formats as date and puts the 1900 date in there.  Also
if a cell has text it throws an error of data type mismatch. Any help
would be appreciated.
lar.- Hide quoted text -

- Show quoted text -
Click to expand...

Sweet - thank you very much!
 
Back
Top