Shorten a formula

[YasserKhalil]

Hello everyone
I have a formula that has a lot of conditions based on the values in columns C and D and based on the day name in column A
The formula is too long and I am trying to make it shorter

[Toranaga]

Hi,

Maybe you tell us what you want to do.

Why do those differences occur?
Between 08:00 and 20:00 there are 12 hours.
Why do 13 hours appear to you? an so on ...

[YasserKhalil]

Because it is multiplied by 1.25 as for all the days except Saturday and Sunday
And it is multiplied by 2 if the day is Friday. And it is multiplied by 1.5 if the day is Saturday.

[Toranaga]

All times shown are 08:00 - 20:00
Are there other working hours (eg: 22:00 - 06:00)?
If they are not, you can simplify the calculation formula.

[YasserKhalil]

There are different times in the real file.

[YasserKhalil]

I could use LET function and this is the solution

Code: Select all

=LET(X,((D3-C3+(D3<C3))*24),Y,IF(X<=8,X,X-(X-8)+(X-8)),IF(D3="","",IF(WEEKDAY(B3)=6,Y*2,IF(WEEKDAY(B3)=7,Y*1.5,IF(X<=8,X,X-(X-8)+(X-8)*1.25)))))

But the file will be used in older versions 2013 .. so LET will not work
Is there an alternative?

[Toranaga]

I had asked if the work schedule could be overnight.
If not, the initial formula could be shortened (without checking if you work from one day to the next)
But I see you found a formula with LET (which can be reduced ...: D)

[YasserKhalil]

Yes the work could be overnight for some people. The LET is working on my office but the file will be shared and sent to someone who doesn't have office 365 and this LET will not work for him.

[YasserKhalil]

Any ideas in this topic?

[HansV]

Here are two options: a single formula and a formula using a helper column as replacement for LET.

Sample.xlsx

[YasserKhalil]

Amazing my tutor. Thanks a lot.

[YasserKhalil]

Can you please have a look at cells C4:D4 .. This is a case that I couldn't include as the person would attend 24 hours from 8 AM in a day to 8 AM the next day
How can this be included?

[HansV]

You should either store the date and time in columns C and D for all rows, or for none at all. Which do you prefer?

[YasserKhalil]

I am not sure what is the most perfect .. But I choose to store date and time in columns C and D to get all the possible occurrences.

[HansV]

See the attached version.

New Sample.xlsx