Click here to Skip to main content
15,614,138 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
The program shown below is a VBA excel program designed to calculate an approximate retirement date for an employee database. I was given the valuation date = Hoy, the Age of each employee in months = EdadMeses, and the number of fees each employee has Paid = Coutas. Starting at age 55 and 1 month (660 months) an employee can retire if and only if they fulfill the number of fees required which are 395 when the employee turns 661 months the fee requirement is now 394, and so on. Assuming that each upcoming month every employee will pay one fee I want to calculate the earliest retirement date for each employee. My questions are: When I run the program the answer it gives me is a time. How can I fix the coding so that the macro gives me a DATE. I used the formula DateAdd("m", (number of fees requirements) - (number of fees that the employee has), valuation date). However, If anyone has a better way to do it or has an understanding of why the formula is giving me a time it would be really helpful.

What I have tried:

Sub If_And_Else()

Dim EdadMeses As String
Dim Cuotas As String

Dim Jubilacion As Date
Dim FechaAprox As Date

EdadMeses = Range("E2").Value
Cuotas = Range("B2").Value

IntervalType = "m"
Hoy = #6/28/2022#
Restante = Coutas
Jubilacion = Range("G2")

If EdadMeses > 779 Then
Jubilacion = Hoy
ElseIf EdadMeses = 660 And Cuotas <= 395 Then
Jubilacion = DateAdd("m", 395 - Restante, Hoy)

ElseIf EdadMeses = 661 And Cuotas <= 394 Then
Jubilacion = DateAdd("m", 394 - Restante, Hoy)

ElseIf EdadMeses = 662 And Cuotas <= 393 Then
Jubilacion = DateAdd("m", 393 - Restante, Hoy)

ElseIf EdadMeses = 663 And Cuotas <= 392 Then
Jubilacion = DateAdd("m", 392 - Restante, Hoy)

ElseIf EdadMeses = 664 And Cuotas <= 391 Then
Jubilacion = DateAdd("m", 391 - Restante, Hoy)

End If

End Sub
Updated 29-Jun-22 4:37am
Richard MacCutchan 29-Jun-22 3:18am    
What do you mean by, the answer it gives me is a time?

1 solution

Your code does not produce a "time" for me, it produces a Date. Therefore the problem is likely to be either with your data or your environment.

I can get it to output an integer if I put Jubilacion into a cell that is formatted as General or I can get it to display as a time if I change the format of the cell to Time. I can also get it as a Time if the Range G2 contains a time and E2 < 779 and B2 > 391

Impossible to tell what you are doing as your code discards the results and does nothing with them. I can only suggest that you use the debugging tools available (e.g. Watch window, Immediate window) to work out what is going wrong.

Other things that may be causing you problems/you could be tidier with:-

1. See this code
Restante = Coutas
I suspect that is meant to match the variable you have declared and should read
Restante = Cuotas
You can avoid runtime errors like that by always using
Option Explicit
at the start of your modules. You can get the IDE to do this by default by ensuring Tools, Options, Editor, Require Variable Declaration is ticked.

2. You have not declared the variables IntervalType, Hoy, or Restante meaning they are all of type Variant. You have declared Cuotas as a String when it is quite clearly meant to be an Integer. VBA is quite forgiving but I have found through bitter experience that it is best to explicitly state which type (and make sure it is the correct type!) throughout any calculations, and not to assume defaults either.

3. Your hard-coded value Hoy = #6/28/2022# assumes the date is in US format - it is better practice to use less ambiguous date formats e.g. use
Hoy = #2022-06-28#
Incidentally, and this is probably no more than personal preference, I dislike the use of # to define dates in VBA and would use
Hoy = CDate("2022-06-28")
Hoy = DateSerial(Year:=2022, Month:=6, Day:=28)
Hoy = DateValue("2022-06-28")
4. You create a variable IntervalType, assign it a value of "m" but then don't use it in any of your calculations. Code Tidily and many potential errors simply go away!
Share this answer
fabiola Risi 29-Jun-22 11:50am    
Thank you so much this was really helpful. I just wanted to know what you meant when you said that I did not use "m" in any of my calculations?
CHill60 29-Jun-22 12:34pm    
You do use "m" but not the variable IntervalType .. for example you use
Jubilacion = DateAdd("m", 395 - Restante, Hoy)
but you could have used
Jubilacion = DateAdd(IntervalType, 395 - Restante, Hoy)
OR just remove the line
IntervalType = "m"
altogether as you do not use the variable anywhere.
Is that clearer?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900