Avatar of Thomas Stockbruegger
Thomas Stockbruegger
Flag for Germany asked on

Need some help with excel automatic values

Hallo,
I have a question about my excel program.
Excel
From A12 you see I have the dates with day of the week.
Is it possible  when I have a Monday-Thursday to set automatic
in I=7 J=0 K=15 L=30 and N=0,75 O=7,75

and on a Friday I=7 J=0 K=12 L=0 and N=0,25 O=4,75

But I also have to manually change the rows and override the values above by my self.
Is this even possible?
I am new with excel and have no experience at all,
Thank you for your help.
Best regards, Thomas
* Excel AddinsMicrosoft Excel* Excel templatesMicrosoft Office

Avatar of undefined
Last Comment
Saqib Husain

8/22/2022 - Mon
Saqib Husain

You can use a formula like this


=if(WEEKDAY(TODAY(),2)<5,Mon-Thu value,Fri-Sun value)


To override, simply type in the value so that there is no formula in that cell.


Geekamo

@Saqib Husain, Syed


Shouldn't "TODAY()" actually be referencing the cells within column A?


(Unless I am misunderstanding the question, then I apologize for jumping in.)


=IF(WEEKDAY(A12,2)<5,Mon - Thu Value Goes Here,Fri - Sun Value Goes Here)

Open in new window

Saqib Husain

Geekamo, spot-on.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Thomas Stockbruegger

ASKER
=if(WEEKDAY(TODAY(),2)<5,Mon-Thu value,Fri-Sun value)
thank you for your answer.
I am new with excel and have no experience at all,
How does that work? Where do I have the formula
Saqib Husain

Enter this formula in I12


=if(WEEKDAY($A12,2)<5,Mon-Thu value,Fri-Sun value)


You can copy it from here and paste it but remember to insert the correct values.


Copy the formula from I12 to all cells from J12 to L12 and then from N12 to O12


Now change the values in each of these cells to the appropriate value.


Now select J12 to L12 and press ctrl-c


Select J12 to J100 (or whichever your last row is) press ctrl-v


Do the same copy-paste for N12 to O12



Geekamo

@Thomas Stockbruegger,


You would place the formula into each of the columns you reference in your OP.


I12 = IF(WEEKDAY(A12,2)<5,7,7)

J12 = IF(WEEKDAY(A12,2)<5,0,0)

K12 = IF(WEEKDAY(A12,2)<5,15,12)

L12 = IF(WEEKDAY(A12,2)<5,30,0)

N12 = IF(WEEKDAY(A12,2)<5,0.75,0.25)

O12 = IF(WEEKDAY(A12,2)<5,7.75,4.75)


and then copied down...


(Keep in mind, if column A contains Saturday/Sunday dates - they will share the same value as Friday.)

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Geekamo

Additionally, the TRUE/FALSE values IN your formula should reference a decimal (not a comma).


@Saqib Husain, Syed - please correct me if I am wrong. I am certainly no expert. :-)

Saqib Husain

Geekamo, you are right. All commas and periods should be interchanged in our formulas.

Thomas Stockbruegger

ASKER
Excel
will get error...will not work
also with =if(WEEKDAY($A12,2)<5,Mon-Thu value,Fri-Sun value)
Excel1.bmp
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Geekamo

@ Thomas Stockbruegger


Can you attach your workbook - with any/all private information removed?

Saqib Husain

Try


=if(WEEKDAY($A12.2)<5.7.7)

Thomas Stockbruegger

ASKER
Here is a short version of the program
Thank you.
Zeiterfassung-2020.01-Expert-Exchang.xls
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Thomas Stockbruegger

ASKER
Try



=if(WEEKDAY($A12.2)<5.7.7)
same result....error
SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Geekamo

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Thomas Stockbruegger

ASKER
=WENN(WOCHENTAG(A12;2)<5;7;7)
Geekamo my excel shows me this formula.
Thats works great. Thank you very much.
Geekamo

Fabulous, glad you got it working!

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Thomas Stockbruegger

ASKER
Thank you Saqib Husain, Syed, yours works fine, too.
Thomas Stockbruegger

ASKER
Thank you both for your great help.
Have a nice day.
Best regards from Germany
Thomas
Saqib Husain

Hope to see more of you. Best regards from Pakistan.

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.