Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem in date time picker size | Excel Discussion (Misc queries) | |||
merging columns - one date, one time | Excel Worksheet Functions | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
HELP - Date Range In 1 Cell Calculation | Excel Worksheet Functions | |||
Date and Time Macro | Excel Discussion (Misc queries) |