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!
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))
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:.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.