Help Me!!!!

  • Thread starter Thread starter wcmash
  • Start date Start date
W

wcmash

Can anyone help I need to know how to find out if a certain weekday fall
between 2 dates.:confused:
 
Hi wcmash!

Here's an excerpt from Chip Pearson's page on Weekday calculations:

Extract>>>
If you need to return the number of Monday's (or any other day) that
occur within an interval between two dates, use the following Array
Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)

<<End Extract

Note especially that this is an array formula entered by pressing and
holding down Ctrl and Shift and then pressing Enter.

For more Weekday formulas see:

http://www.cpearson.com/excel/DateTimeWS.htm
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Okay this is the problem my company has 48 hour turnarounf time So lets
say the beggining date is 3/12/04 4:00p.m which is a friday and the
ending date is 3/16/04 which is a tuesday. I need to know how many
hours are between the 2 dates but without the weekends.
 
Hi
do you also have specific business hours. that is count only houer
between 08:00 and 17:00 for a weekday??
 
Hi wcmash!

I should have gone on to say, that to find out if there is a
particular day in the period use:

=IF(SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))<>
0,TRUE,FALSE)

As an example:
A2:
23-Feb-2004
B2:
27-Feb-2004
C2:
2 [Weekday number for Monday]
D2:
=IF(SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))<>
0,TRUE,FALSE)
Returns: TRUE because 23-Feb-2004 was a Monday.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Norman Harker said:
Hi wcmash!

Here's an excerpt from Chip Pearson's page on Weekday calculations:

Extract>>>
If you need to return the number of Monday's (or any other day) that
occur within an interval between two dates, use the following Array
Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)

<<End Extract

Note especially that this is an array formula entered by pressing and
holding down Ctrl and Shift and then pressing Enter.

For more Weekday formulas see:

http://www.cpearson.com/excel/DateTimeWS.htm
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
wcmash > said:
Can anyone help I need to know how to find out if a certain
Click to expand...
weekday
Click to expand...
 
Hi Frank!

Looks like you were wise to ask for more details :)
Think I'll wait for the answers to these follow throughs.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Frank Kabel said:
Hi
do you also have specific business hours. that is count only houer
between 08:00 and 17:00 for a weekday??
Click to expand...
 
Norman Harker said:
Hi Frank!

Looks like you were wise to ask for more details :)
Think I'll wait for the answers to these follow throughs.
Click to expand...

Norman
just a little bit lazy today :-)
and after seeing your formulas I assumed better to wait as the formulas
probably will get more complicated (as the requirements will definetly
grow...)

Best regards
Frank
 
Hi wcmash!

Try the following:

=((NETWORKDAYS(A2,B2)-2)+(1-MOD(A2,1))+MOD(B2,1))*24
Format General

Or

=(NETWORKDAYS(A2,B2)-2)+(1-MOD(A2,1))+MOD(B2,1)
Format [hh]:mm

Example:

Start time
A2: 20-Feb-2004 4:00:00 PM
End Time
B2: 27/02/2004 2:00:00 PM

=NETWORKDAYS(A2,B2) returns the number of whole working days inclusive
of start day and end day. So deducting 2 gives the whole working days
exclusive of those days. (1-MOD(A2,1) gives the decimal part of a day
worked on the start day and MOD(B2,1) gives the decimal part of a day
worked on the end day.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top