Count - Days and Times

SteveWebb

Board Regular
Joined
Feb 20, 2002
Messages
73
Hi all,

I have 2 columns, col A: Date with time i.e.(21 Oct 06 11:34)
col B: Manually inputted Day i.e.(Sun)
The range is from 01st Jan to 30th Sept, but this will be ongoing.

Is it possible to automate (col B)the Day from info in col A?

Can I also count the number of say Sundays in a given range, say last quarter. Would this also be possible with the time. I am wanting to do this to help with problem solving.

I have spent many a frustrated hour trying to get my head around this, any help will be most welcome.

Steve
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,430
Office Version
  1. 365
Platform
  1. Windows
Is it possible to automate (col B)the Day from info in col A?
Here is one way.
1. Formula in B2 (copied down): =A2
2. Select Column B
3. Format|Cells...|Number|Category: Custom|In the Type: box, replce what is there with ddd|OK
Can I also count the number of say Sundays in a given range, say last quarter
Yes, but you would need to tell us more about just what you mean by last quarter.
Are the quarters Jan - Mar etc?
Is last quarter related to today's date (so last quarter would be Jul - Sep)? or a date on the sheet?
Mr Excel.xls
ABCD
1Date/TimeDay
201/01/2006 11:00Sun
302/01/2006 11:30Mon
403/01/2006 12:00Tue
504/01/2006 12:30Wed
605/01/2006 13:00Thu
706/01/2006 13:30Fri
807/01/2006 14:00Sat
908/01/2006 14:30Sun
1009/01/2006 15:00Mon
1110/01/2006 15:30Tue
1211/01/2006 16:00Wed
1312/01/2006 16:30Thu
1413/01/2006 17:00Fri
1514/01/2006 17:30Sat
1615/01/2006 18:00Sun
1716/01/2006 18:30Mon
1817/01/2006 19:00Tue
1918/01/2006 19:30Wed
20
Dates
 

SteveWebb

Board Regular
Joined
Feb 20, 2002
Messages
73
Hi Peter,
Many thanks for your quick reply, Yes Quarters are 01st July -30th Sept etc.
Cheers
Steve
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,430
Office Version
  1. 365
Platform
  1. Windows
Steve

You didn't fill out much more deatil so this is a bit of a guess. See if it is any help. Note that I have changed my sample data from the previous post.

E1:
Code:
=TODAY()
E2:
Code:
=EDATE(E3,-3)+1
(requires Analysis ToolPak to be installed & activated - check by Tools|Ad-Ins... and ensure Analysis ToolPak is there and ticked)
E3:
Code:
=DATE(YEAR(E1),FLOOR(MONTH(E1),3)+1,1)-1
E4:
Code:
=SUMPRODUCT(--(WEEKDAY(A2:A20)=1),--(A2:A20>=E2),--(A2:A20<E3))

Just to clarify, I have coloured all the Sundays in my sample data. The formula counts the green ones but not the yellow ones.
Mr Excel.xls
ABCDEF
1Date/TimeDayToady's Date22/10/2006
224/09/2006 11:00SunStart of Last Quarter01/07/2006
302/09/2006 11:30SatEnd of Last Quarter30/09/2006
403/07/2006 12:00MonSundays in Last Quarter4
504/06/2006 12:30Sun
605/07/2006 13:00Wed
716/07/2006 13:30Sun
827/07/2006 14:00Thu
907/08/2006 14:30Mon
1020/08/2006 15:00Sun
1129/08/2006 15:30Tue
1210/09/2006 16:00Sun
1320/09/2006 16:30Wed
1401/10/2006 17:00Sun
1512/10/2006 17:30Thu
1623/10/2006 18:00Mon
1703/11/2006 18:30Fri
1814/11/2006 19:00Tue
1925/11/2006 19:30Sat
2026/11/2006 20:00Sun
Sundays
 

SteveWebb

Board Regular
Joined
Feb 20, 2002
Messages
73

ADVERTISEMENT

Hi Peter,
Brilliant many thanks

Steve
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,430
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,
Brilliant many thanks

Steve
Glad to help. :biggrin:

Now that we apparently have that sorted, I just wanted to comment about column B. Note that we did not use column B in getting the answer, so you may not need it. If you do want to know the day of the week, are you aware that if you format column A as a Date, you can look through the list of available Date formats and you will find one that includes the day name as well as the date? Again if you don't want the full name (like Sunday) you can create a custom format with the dddd part reduced to ddd.
 

SteveWebb

Board Regular
Joined
Feb 20, 2002
Messages
73

ADVERTISEMENT

Hi Peter,
Can I use this formula to count the Mon,Tues etc if reqd as well as the number of Sundays. Am trying to use this for problem solving?

Steve
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,430
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,
Can I use this formula to count the Mon,Tues etc if reqd as well as the number of Sundays. Am trying to use this for problem solving?

Steve
Yes, in the SUMPRODUCT formula, replace the =1 with
=2 for Mon
=3 for Tue
etc
 

SteveWebb

Board Regular
Joined
Feb 20, 2002
Messages
73
Peter,
Theres hope for me yet?
Many thanks and I now understand the formula/s.
Absolutely brill, many thanks for taking the time.

Steve
 

Forum statistics

Threads
1,136,420
Messages
5,675,737
Members
419,585
Latest member
popsin

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