PDA

View Full Version : Operation with dates



rodtt
07-24-2013, 02:46 PM
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
07-24-2013, 03:33 PM
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
07-24-2013, 03:40 PM
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
07-24-2013, 04:04 PM
Cool formula, AND, I didn't consider if the date was before Jan 15.

Think I'll edit my post to add that.

rollis13
07-24-2013, 04:12 PM
That's the reason why I didn't post earlier :tongue:, couldn't understand why a simple formula didn't work for the entire year and testing takes time http://www.vbaexpress.com/forum/images/smilies/doh.gif.

rodtt
07-24-2013, 04:53 PM
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
07-25-2013, 02:54 AM
Glad we've been of some help :hi:.