Need help on Date formula in excel

[spycein]

Hi Everyone,

I have a following table
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Quarter[/TD]
[TD]Activity Start Date[/TD]
[TD]Activity End Date[/TD]
[TD]Budget Year[/TD]
[/TR]
[TR]
[TD]Q2-2017[/TD]
[TD]5th May 2017[/TD]
[TD]26th June 2017[/TD]
[TD]2017[/TD]
[/TR]
</tbody>[/TABLE]





i want to populate Column No. "Quarter" with formula which will return the value as Quarter 1/Quarter 2/Quarter 3/Quarter 4 with suffix of budget year depend on the combination values of column no. "Activity Start Date" & "Activity End Date" fields.

For example,
if "Activity Start date" & "Activity End Date field contain dates between 1st Jan 2017 to 31st March 2017 then column no."Quarter" will return the value as "Quarter 1-2017"

similarly, if "Activity Start date" & "Activity End Date field contain dates between 1st April 2017 to 31st June 2017 then column no."Quarter" will return the value as "Quarter 2-2017"

Thank you so much in advance.

Regards
Shib

 

[S_Wish]

Hi, Shib!

Are there a posibility: activity starts in Q2 and ends in Q3? and what should be written there?

 

[spycein]

No.. There wont be any possibilities like that. Activities would be completed in the same quarter.
Thank you so much for the prompt reply.

 

[S_Wish]

Your task can be simplified to: get activity start mounth and check it
It`s not the best, but it works for me. I think there is simplier answer, but I don`t know how to play with dates

Sub QuarterCheck()


Dim str As String
Dim i As Integer
For i = 2 To Range("b65000").End(xlUp).Row
'takes year from B column and put into D column
'uncomment if neccessary
'Range("d" & i).Value = Mid(Range("b" & i).Value, Len(Range("b" & i).Value) - 4)
'gets mounth
str = Trim(Mid(Range("b" & i).Value, 5, Len(Range("b" & i).Value) - 8))
'check mounths
        Select Case str
            Case "January", "February", "March"
                Range("a" & i).Value = "Q1-" & Range("d" & i).Value
            Case "April", "May", "June"
                Range("a" & i).Value = "Q2-" & Range("d" & i).Value
            Case "July", "August", "September"
                Range("a" & i).Value = "Q3-" & Range("d" & i).Value
            Case "October", "November", "December"
                Range("a" & i).Value = "Q4-" & Range("d" & i).Value
            Case Else
            '
        End Select
Next i

End Sub

 

[spycein]

Hi S_Wish,

Thank you so much for the reply.

Could you please attach an excel sheet consisting with the above code, so that i can get a clarity on the formula and also could check how does it work.

Thanks a ton.

 

[S_Wish]

it is not allowed to attach files. all you need is to put that code into a VBA module, and run it.
if you do not know how to open vba - google.

 

[FormR]

Hi, here is a formula you can try - this assumes that the dates are true excel dates (not text).

Excel 2013
	A	B	C	D
1	Quarter	Activity Start Date	Activity End Date	Budget Year
2	Q2-2017	05-May-17	26-Jun-17	2017
Sheet1

Cell Formulas
Range	Formula
A2	="Q"&ROUNDUP(MONTH(B2)/3,0)&"-"&D2