15,998,093 members
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:

VB
```Sub If_And_Else()

Dim Cuotas As String

Dim Jubilacion As Date
Dim FechaAprox As Date

Cuotas = Range("B2").Value

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

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```
Posted
Updated 29-Jun-22 4:37am
v2
Richard MacCutchan 29-Jun-22 3:18am
What do you mean by, the answer it gives me is a time?

## Solution 1

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
VB
`Restante = Coutas`
I suspect that is meant to match the variable you have declared and should read
VB
`Restante = Cuotas`
You can avoid runtime errors like that by always using
VB
`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
VB
`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
VB
`Hoy = CDate("2022-06-28")`
or
VB
`Hoy = DateSerial(Year:=2022, Month:=6, Day:=28)`
or
VB
`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!

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?