IF Formula

[jgp19]

Can anyone help to write to if formula for below scenario?

employee joining date having less than date 01-06-2024 in a cell then give value of 30 days having in a cell and if employee joining date having equal or higher date that is 30-06-2024, then give value subtracting 30-06-2024-02-06-2024.

Please help.

 

[awoohaw]

welcome to the forum. Can you give some examples with expected results?

 

[jgp19]

welcome to the forum. Can you give some examples with expected results?

I have a employees joining date in one column A, total number of days in a month i.e 30 in column B. I want to have actual work days in column C. Expected results should be if joining date mentioned in column A less than 1St June 2024, then I should value of 30 days in the column C. If joining date is higher than or equal to 1St June 2024 then I need a value of days after subtracting 6/30/2024 - any higher or equal date of 6/01/2024.

 

[awoohaw]

Still sort of unsure what you want. But try this:

Book1
	A	B	C
1
2	Current Month		2024-06-01
3	Current Month Formatted as mmm		Jun
4	Join	Days This Month	Work Days This Month
5	2024-06-13	30	12
6	2024-01-22	30	20
Sheet4

Cell Formulas
Range	Formula
C3	C3	=C2
B5:B6	B5	=EOMONTH($C$2,0)-EOMONTH($C$2,-1)
C5:C6	C5	=NETWORKDAYS(MAX(A5,EOMONTH($C$2,-1)+1),EOMONTH($C$2,0))

NOTE: If you are concerned with Holidays or weekends that are not Sat and Sun you can use the NETWORKDAYS.INTL function.

 

[jgp19]

 

[awoohaw]

not sure what that means. What are the answers you want to see in C5 and C6?

 

[jgp19]

I want actual number of days worked in c5 and c6.
in case the employees joined before 1st of June 2024 it should show me the number of worked days as 30 only.
in case the employee joined in any day of June 2024 it should show actual worked days in the month of June.

 

[awoohaw]

I want actual number of days worked in c5 and c6.

in case the employee joined in any day of June 2024 it should show actual worked days in the month of June.

You really need to give a concrete expected number:

Book1
	A	B	C
1	2024-06-01	Month End Date:	2024-06-30
2	Current Month		2024-06-01
3	Current Month Formatted as mmm		Jun
4	Join	Days This Month	Work Days This Month
5	2024-06-13	30	2
6	2024-01-22	30	30
Sheet4

Cell Formulas
Range	Formula
C1	C1	=EOMONTH(C2,0)
C3	C3	=C2
B5:B6	B5	=EOMONTH($C$2,0)-EOMONTH($C$2,-1)
C5:C6	C5	=IF(A5>$A$1,NETWORKDAYS(A5,TODAY()),30)

 

[jgp19]

Thanks for the above formula it is almost ok, however result in c5 should be subtract of (2024-06-30 - 2024-06-13) = 18 days.
this requirement is for payroll where we use month end to count number of working days of employee, so if we use "today" in formula it gives result as of today worked day but i want to see work days as month end date.

 

[Phuoc]

Try this:

Book1
	A	B	C
1	1/6/2024	Month End Date	30/6/2024
2
3
4	Join	Days This Month	Work Days This Month
5	13/6/2024		18
6	22/1/2024		30
Sheet1

Cell Formulas
Range	Formula
C1	C1	=EOMONTH(A1,0)
C5:C6	C5	=$C$1-MAX(A5,$A$1)+1