how to determine blocks of time in a date range
 

In a given date/time range I want to determine how many Sundays there are and
on the other full days how many 16 & 8 hourblocks there are. Then I want to
total each. Plus on the start and finish days determine how the time breaks
into each block

how to determine blocks of time in a date range
 

Hi!

One way to count the number of specific weekdays between two dates
(inclusive):

A1 = start date
B1 = end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))

I have no idea what you want in the rest of your post!

Biff

"hydro1guy" wrote in message
...
In a given date/time range I want to determine how many Sundays there are
and
on the other full days how many 16 & 8 hourblocks there are. Then I want
to
total each. Plus on the start and finish days determine how the time
breaks
into each block

how to determine blocks of time in a date range
 

Thanks for your help it has solved one problem for me .

on a given time range of a day (7:00-23:00) or any other range in a day, how
would I determine the number of hours that fall in between 6:00-22:00,
00:00-6:00, and 22:00-24:00.

"Biff" wrote:

Hi!

One way to count the number of specific weekdays between two dates
(inclusive):

A1 = start date
B1 = end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))

I have no idea what you want in the rest of your post!

Biff

"hydro1guy" wrote in message
...
In a given date/time range I want to determine how many Sundays there are
and
on the other full days how many 16 & 8 hourblocks there are. Then I want
to
total each. Plus on the start and finish days determine how the time
breaks
into each block

how to determine blocks of time in a date range
 

on a given time range of a day (7:00-23:00) or any other range in a day,
how
would I determine the number of hours that fall in between 6:00-22:00,
00:00-6:00, and 22:00-24:00.

Simply subtract the earlier time from the later time:

A1 = 7:00
B1 = 23:00

=B1-A1

If the later time spans past midnight:

A1 = 22:00
B1 = 2:00

=B1-A1+(B1<A1)

Biff

"hydro1guy" wrote in message
...
Thanks for your help it has solved one problem for me .

on a given time range of a day (7:00-23:00) or any other range in a day,
how
would I determine the number of hours that fall in between 6:00-22:00,
00:00-6:00, and 22:00-24:00.

"Biff" wrote:

Hi!

One way to count the number of specific weekdays between two dates
(inclusive):

A1 = start date
B1 = end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))

I have no idea what you want in the rest of your post!

Biff

"hydro1guy" wrote in message
...
In a given date/time range I want to determine how many Sundays there
are
and
on the other full days how many 16 & 8 hourblocks there are. Then I
want
to
total each. Plus on the start and finish days determine how the time
breaks
into each block

how to determine blocks of time in a date range
 

Hmmm....

I think I misunderstood, so disregard that last reply.

Take a look at this:

http://www.mvps.org/dmcritchie/excel/datetime.htm#core

You wouldn't believe how complicated it is to do what you want. This is the
only site I've ever seen that even attempts to address your question.

Biff

"Biff" wrote in message
...
on a given time range of a day (7:00-23:00) or any other range in a day,
how
would I determine the number of hours that fall in between 6:00-22:00,
00:00-6:00, and 22:00-24:00.

Simply subtract the earlier time from the later time:

A1 = 7:00
B1 = 23:00

=B1-A1

If the later time spans past midnight:

A1 = 22:00
B1 = 2:00

=B1-A1+(B1<A1)

Biff

"hydro1guy" wrote in message
...
Thanks for your help it has solved one problem for me .

on a given time range of a day (7:00-23:00) or any other range in a day,
how
would I determine the number of hours that fall in between 6:00-22:00,
00:00-6:00, and 22:00-24:00.

"Biff" wrote:

Hi!

One way to count the number of specific weekdays between two dates
(inclusive):

A1 = start date
B1 = end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))

I have no idea what you want in the rest of your post!

Biff

"hydro1guy" wrote in message
...
In a given date/time range I want to determine how many Sundays there
are
and
on the other full days how many 16 & 8 hourblocks there are. Then I
want
to
total each. Plus on the start and finish days determine how the time
breaks
into each block