Days to days and months

[MartinCarlsson]

Hi
I have x number of days, and I need it to make it to days and months.

Example.
x = 45

days -> 15
months -> 1

I know there is more than one way and Im looking for the best way to do it for my program.
Any suggestions?

//Martin

 

[gaj104]

You are assuming only 30 days in a month?

If so, then =+FLOOR(A1/30,1)&" Month "&+A1-(FLOOR(A1/30,1)*30)&" days"

 

[MartinCarlsson]

Thank you very much... I need to be more specific! Sorry.

How does I do it ion VBA?

 

[gaj104]

Try

Dim x As Long

x = 45

Call MsgBox(Int(x / 30) & " Month " & x - (Int(x / 30) * 30) & " days")

 

[hurgadion]

Hi,
I suggest more precise Solution, In A3 there is an array formula of the form (a number of Months):

=MAX(IF(DATE(YEAR(TODAY()),MONTH(TODAY())+ROW(A1:A1000),DAY(TODAY()))<=TODAY()+A1,ROW(A1:A1000),""))

and in B3 there is a formula (a remaining part of Minutes):

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+A1)-DATE(YEAR(TODAY()),MONTH(TODAY())+A3;DAY(TODAY()))

Best regards.

 

[MartinCarlsson]

Try

Dim x As Long

x = 45

Call MsgBox(Int(x / 30) & " Month " & x - (Int(x / 30) * 30) & " days")

Thanks
I need to make the .. number of month, even if I have 1,000 days.

And if I exchange 30 for 365.2 / 12, it returns days like: 14,5666666666667

Sub heya()
    Dim x As Long
    Dim daysInMonths As Variant
    
    daysInMonths = 365.2 / 12
    x = 45
    
    Debug.Print (Int(x / daysInMonths) & " Month " & x - (Int(x / daysInMonths) * daysInMonths) & " days")


End Sub

Immediate:
1 Month 14,5666666666667 days

Hi,
I suggest more precise Solution, In A3 there is an array formula of the form (a number of Months):

=MAX(IF(DATE(YEAR(TODAY()),MONTH(TODAY())+ROW(A1:A1000),DAY(TODAY()))<=TODAY()+A1,ROW(A1:A1000),""))

and in B3 there is a formula (a remaining part of Minutes):

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+A1)-DATE(YEAR(TODAY()),MONTH(TODAY())+A3;DAY(TODAY()))

Best regards.

Thanks.
Can you show it in VBA?

 

[hurgadion]

Try maybe using the macro of the form:

Sub lala()
Dim i&, z&, x&, y&

x = 45
Cells(1, 4).Formula = "=TODAY()"
y = Cells(1, 4).Value

For i = 1 To 1000
  If DateSerial(Year(y), Month(y) + i, Day(y)) <= DateSerial(Year(y), Month(y), Day(y) + x) Then
    z = i
  Else
    Exit For
  End If
Next i

Cells(1, 3).Value = z
Cells(1, 4).Value = y + x - DateSerial(Year(y), Month(y) + z, Day(y))
Cells(1, 4).NumberFormat = "General"
End Sub

Best regards.

 

[MartinCarlsson]

bump

 

[MartinCarlsson]

Try maybe using the macro of the form:

Sub lala()
Dim i&, z&, x&, y&

x = 45
Cells(1, 4).Formula = "=TODAY()"
y = Cells(1, 4).Value

For i = 1 To 1000
  If DateSerial(Year(y), Month(y) + i, Day(y)) <= DateSerial(Year(y), Month(y), Day(y) + x) Then
    z = i
  Else
    Exit For
  End If
Next i

Cells(1, 3).Value = z
Cells(1, 4).Value = y + x - DateSerial(Year(y), Month(y) + z, Day(y))
Cells(1, 4).NumberFormat = "General"
End Sub

Best regards.

Thanks!

Your code is a bit confusing.
Why do you use "=TODAY()" instead og Now()
Why do you loop?
Why do you end the loop with an if statement instead of the usual way?

 

[vds1]

Try this In Immediate Window

Sub Test_Immediate_Window()
Dim IntDays As Long
IntDays = 100000000
Debug.Print Evaluate("=+FLOOR( " & IntDays & "/30,1)& "" Month "" & +" & IntDays & "-(FLOOR( " & IntDays & "/30,1)*30)&"" days""")
End Sub