For example,
A1= mon
B1= 6.30am
Mon to fri 6am to 9.30am &
Mon to sun & public holiday 6pm to 12pm, the if function will return a value of 1 when input cell A1 & B1 fall within this range.
The rest which is out of these 2 range, the function will return a value of 0,
can I squeeze all these into 1 function with the bold text being my logical test?
but first, im having problem with the date text in for IF function before solving the time...
=IF(A1=TEXT(Monday,ddd),1,0) this doesn't work??
Last edited by hhhhhhzzzz; 08-24-2015 at 03:42 AM.
I dont really know what you are asking, but that formula needs to be...
=IF(TEXT(A1,"ddd")="Mon",1,0)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
rephrased my question, ok thanks, will go try it out now.
Hi,
Try this which should account for everything except public holidays:
Formula:
Please Login or Register to view this content.
Note that I've assumed you meant 12midnight not 12pm for the second requirement. B1 needs to be formatted as time for this to work. Excel treats time as a number, so 0.5 is 12noon, 0.25 is 6am, etc - that's why the formula includes 6/24 etc - you can change these to 0.25 etc if you want - I just wanted it to be clear.
To account for public holidays, you'd need to have A1 formatted as a date using custom format 'ddd' and compare the date with a list of public holidays in another range.
Hope that's of some help.
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
How can I amend this part?
=IF(TEXT(A1,"ddd")="Mon",1,0)
When I want to add from mon to fri?
woah, thanks for making an effort to come out with this long formula... really appreciate it! Shall try it out (:
Perhaps this?
=IF(LEFT(TEXT(A8,"ddd"),1)<>"s",1,0)
How to I amend the formula if I want it to have 12am to be included, in other words if B1 is 12am, the function will return a value of 1
I tried to add in = sign at the last logical test before B1<=1, but it doesn't work..
Please Login or Register to view this content.
the rest B1>=(6/24),B1<=(9.5/24) works when i want the number to be included.
Last edited by hhhhhhzzzz; 08-23-2015 at 04:07 PM.
If A1 contains a date you can reduce this:Originally Posted by hhhhhhzzzz
OR(TEXT(A1,"ddd")="Mon",TEXT(A1,"ddd")="Tue",TEXT(A1,"ddd")="Wed",TEXT(A1,"ddd")="Thu",TEXT(A1,"ddd")="Fri")
To this:
WEEKDAY(A1,2)<6
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Still having problem with the the value of 12am... T.T
putting an equal sign for logical test B1<=1 does not return me the value of 1 if B1 is 12am...
Anyone knows what's the problem?
May be the below change in the same formula would do it.
=IF(OR(AND(OR(TEXT(A1,"ddd")="Mon",TEXT(A1,"ddd")="Tue",TEXT(A1,"ddd")="Wed",TEXT(A1,"ddd")="Thu",TEXT(A1,"ddd")="Fri"),B1>(6/24),B1<(9.5/24)),AND(OR(B1=0,B1>(18/24)),OR(B1<1,B1=0))),1,0)
It works! Thank you!
Glad to have helped. If you still need to include public holidays, let us know and I'm sure someone here will be able to work that out for you.
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
Its okay! Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks