Operation with dates

[rodtt]

Hi to all!

I need excel to take a date and return its month if the day is in the second half of the month and the previous one if it's in the first half.
(I.E, 17/05/2013 should return 5, while 14/05/2013 return 4)

If possible, I would like to have a solution with the actual number of days in the month (28, 29, 30 and 31) and 30 days.

Don't know if I can do this without vba, which would be definitely better.

Thanks!

[SamT]

Sub Test()
Dim X
X = MnthNum("7/15/2013")
End Sub

Function MnthNum(Dte As Date) As Long
Dim D As Long
Dim M As Long
Dim Y As Long
Dim Diff As Long
D = Day(Dte) 'Returns day number of month of Dte
M = Month(Dte) 'Returns month number of Dte
Y = Year(Dte) 'I dunno ;)

'Num Days in this month of Dte is same as
'Difference in days between 1st day next month of Dte, and 1st of this month of Dte
Diff = DateSerial(Y, M + 1, 1) - DateSerial(Y, M, 1)

If D > Diff / 2 Then
  MnthNum = M
Else
  MnthNum = M - 1
  If MnthNum = 0 Then MnthNum = 12
End If
End Function

[rollis13]

Could a formula do ?
with your date in A4 in B4 use:

=IF(DAY(A4)>DAY(EOMONTH(A4,0))/2,MONTH(A4),IF(MONTH(A4)=1,12,MONTH(A4)-1))

[SamT]

Cool formula, AND, I didn't consider if the date was before Jan 15.

Think I'll edit my post to add that.

[rollis13]

That's the reason why I didn't post earlier , couldn't understand why a simple formula didn't work for the entire year and testing takes time .

[rodtt]

This formula will sufice. It's working as I desired.
It also helped with another operation I'm doing with dates (taking a mm/yyyy date and adding one month to it)

=DATE(IF(MONTH(D3)=12;YEAR(D3)+1;YEAR(D3));IF(MONTH(D3)=12;1;MONTH(D3)+1);1 )

Thanks to both of you!

[rollis13]

Glad we've been of some help .