PDA

View Full Version : Solved: Problem in Month days Calculation

jammer6_9
02-11-2008, 06:09 AM
I am having a worksheet of Expiration Checklist.

Column A is Item Name
Column B is Shelf Life ( eg. 4 months )
Column C is Production Date ( eg. 5-May-2008 )
Column D is Expiration Date which I calculated as

=Production Date + (30.5 * 4) ' 30.5 as an Average days in a month.

which give me result of 4-Sep-2008 expiration date.

wherein it differ in the actual Case Packaging as follows which I need my worksheet calculation result regardless of the number of days in a month;

Production Date - 5-May-2008
Expiration Date - 5-Sep-2008

xld
02-11-2008, 06:32 AM
=DATE(YEAR(C5),MONTH(C5)+B5,DAY(C5))

but what do you want to do for 31st May?

jammer6_9
02-11-2008, 07:03 AM
So far my query is solved xld... :bow: I just dont know for now what about the 31st... :bug:

=DATE(YEAR(C5),MONTH(C5)+B5,DAY(C5))

but what do you want to do for 31st May?

xld
02-11-2008, 07:04 AM
It will come out as 1st Oct because there is no 31st Sep.

jammer6_9
02-11-2008, 11:37 PM
My concern on the worksheet is to show date base on packaging at least and will not show any expired items OR can show earlier expiration date.

As I am planning to put restriction like;

Dim rng As Range

Set rng = Sheet1.Range("C14:C100")

If ActiveCell.Value = 'here I want to put the condition like if user entered a date thatt is equal to 29th , 30th or 31st then result will be 28th max...

End If

xld
02-12-2008, 11:23 AM
Dim rng As Range

Set rng = Sheet1.Range("C14:C100")

With ActiveCell
If Day(.Value) > 28 Then
.Value = .Value - Day(.Value) + 28
End If
End With

jammer6_9
02-13-2008, 12:18 AM
I am trying to change ActiveCell to Range so that I can put the code in Worksheet_SelectionChange Event but I am not successful in doing it and having "Type Mismatch" pointing to this section ---> If Day(.Value) > 28 Then

Dim rng As Range

Set rng = Sheet1.Range("C14:C100")

With ActiveCell
If Day(.Value) > 28 Then
.Value = .Value - Day(.Value) + 28
End If
End With

xld
02-13-2008, 01:52 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo ws_exit

Application.EnableEvents = False

Set rng = Sheet1.Range("C14:C100")

If Not Intersect(Target, rng) Is Nothing Then
With Target
If Day(.Value) > 28 Then
.Value = .Value - Day(.Value) + 28
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

jammer6_9
02-13-2008, 02:09 AM
Thnks xld... I come up with almost the same code... Not better than your code but it works...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
With Target

If Day(.Value) > 28 Then
.Value = .Value - Day(.Value) + 28

End If

End With
End Sub