Time Based formula

[vijaysharmad]

HI, I am looking for time based formula.Below is the condition to be applied.

If it's saturday and time is between 8.30 AM to Sunday 8.29 AM then pay one Rate
If it's Sunday and time is between 8.30 AM to Monday 4.00 AM then pay one Rate

All the formula to be linked to one cell.

Can some assist on this.

[etaf]

How is you data laid out , a sample sheet would help

Is the date and time in the same cell ?

An IF combined with an AND & Weekday should solve for you
If it a normal weekday is that pay Rate 3 or not interested in rest of week

[vijaysharmad]

Yes, both in same cell. the pay is only for specified time. If those condition is satisfy then count 1 * Rate

[vijaysharmad]

Actdate
5/1/2020 8:25:00 AM
5/1/2020 8:26:00 AM
5/1/2020 8:27:00 AM
5/1/2020 8:29:00 AM
5/1/2020 8:30:00 AM
5/1/2020 8:30:00 AM

[bebo021999]

Assuming A1 is date

try:

Please Login or Register  to view this content.

[vijaysharmad]

Thanks, let me modify a bit.....can you assist in this case

If it's saturday and time is between 8.30 AM to Sunday 8.29 AM then 1 (numerice 1 should appear), else 0
If it's Sunday and time is between 8.30 AM to Monday 4.00 AM then 1 (numerice 1 should appear), else 0

[bebo021999]

Does it work for you?

=IF(WEEKDAY(A1,16)+MOD(A1,1)<1+"8:29:59",0,IF(WEEKDAY(A1,16)+MOD(A1,1)<3+"3:59:59",1,0))

[vijaysharmad]

Hi Bebo, good attempt, however the same is showing Zero for below case

5/1/2020 8:25:00 AM - Here the 1st may is Sunday, then it satisfy my first condition.


In below case, it showing 1 and per the condition it should be Zero.
5/2/2020 8:30:00 AM - Here 2nd May is Monday and time is after 4 AM.

[bebo021999]

5/1/2020 8:25:00 AM - Here the 1st may is Sunday, then it satisfy my first condition....

I wonder if my computer calendar goes wrong? May 1st 2020 is Friday

[AliGW]

May 01 2020 was indeed a Friday.

[vijaysharmad]

Thanks Bebo, your solution worked like magic.

However, I am stuck with one dilemma..... I need your assistance in the below

Assume Cell E2 Assume Cell K2
Weekday Bundle length Rate
saturday and time is between 8.30 AM to Sunday 8.29 AM 20 100
saturday and time is between 8.30 AM to Sunday 8.29 AM 40 200
Sunday and time is between 8.30 AM to Monday 4.00 AM 20 300
Sunday and time is between 8.30 AM to Monday 4.00 AM 40 400

[vijaysharmad]

And Yes, you were right with regard dates.

[bebo021999]

Try to upload a worksheet please

[vijaysharmad]

Sample file attached for your reference

[bebo021999]

Try:

Please Login or Register  to view this content.

[vijaysharmad]

Thanks Bebo, It works....i acknowledge you are Maestro in excel.....

If I want to change the day from Saturday to Friday or Timing from evening 6 PM, then what I need to change in the formula

[bebo021999]

Try to study how WEEKDAY(date,k) works. Im using k=16 for Sat. For friday, k=15.

For "6:00PM" use "5:59:59"

[vijaysharmad]

Hi Bebo, this is not working,

For eg, if it's Saturday it's 4/11/2020 9:48:00 AM, then it is getting picked in my calculation.

The condition here is Pay only from Saturday 6:00:00 PM to Monday 6:00:00 AM

Can you assist me with a solution, the rest of parameters remains same like time and date is available in one cell....no need to worry about 20/40 bundles.