Need help on Date formula in excel

spycein

Board Regular
Joined
Mar 8, 2014
Messages
106
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I have a following table
QuarterActivity Start DateActivity End DateBudget Year
Q2-20175th May 201726th June 20172017

<tbody>
</tbody>





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
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

S_Wish

Board Regular
Joined
Jan 4, 2017
Messages
216
Hi, Shib!

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

spycein

Board Regular
Joined
Mar 8, 2014
Messages
106
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 4, 2017
Messages
216
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
Code:
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
 
Last edited:

spycein

Board Regular
Joined
Mar 8, 2014
Messages
106
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jan 4, 2017
Messages
216
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, here is a formula you can try - this assumes that the dates are true excel dates (not text).

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Quarter</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Activity Start Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Activity End Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Budget Year</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFF00;;">Q2-2017</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">05-May-17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">26-Jun-17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">2017</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A2</th><td style="text-align:left">="Q"&ROUNDUP(<font color="Blue">MONTH(<font color="Red">B2</font>)/3,0</font>)&"-"&D2</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top