# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hello I need to calculate time between two dates and then use a function to use that as part of a larger calculation. I know how to run the calculation between two dates using "datediff" I need help with: If 1 has worked for my company less than 3 mths, 1 gets no sick pay, if 1 has worked for my company more than 3 mths, but less than 12 mths, then 1 gets 2 weeks full sick pay, If 1 has worked more than 12 months, one gets 5 weeks full sick pay. How do I express this in excel 2016 function(s) Is this possible within one cell, using the difference between two dates. Thanks very much.
Solved by F. E. in 37 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/07/2018 - 03:02
Hi
Excelchat Expert 17/07/2018 - 03:03
Hello
Excelchat Expert 17/07/2018 - 03:04
Are you there
User 17/07/2018 - 03:09
Yes
User 17/07/2018 - 03:09
Apologies
User 17/07/2018 - 03:09
I am here
Excelchat Expert 17/07/2018 - 03:09
no issues
User 17/07/2018 - 03:09
I did not see this side of the text
Excelchat Expert 17/07/2018 - 03:09
example > 3 <12 months = 2 weeks sick pay
User 17/07/2018 - 03:10
yes
Excelchat Expert 17/07/2018 - 03:10
salary/52 *2 right
User 17/07/2018 - 03:10
yes
Excelchat Expert 17/07/2018 - 03:10
give me a min to work on the formula
User 17/07/2018 - 03:10
I just cannot figure out what function I can nest within one cell to work this out
User 17/07/2018 - 03:10
thanks
User 17/07/2018 - 03:12
I have tried this nest: =IFS(DATEDIF(D3,\$D\$1,"m")>3,E3/52*9,DATEDIF(D3,\$D\$1,"m")>12,E3/52*39)
User 17/07/2018 - 03:12
but unfortunately it does not work
Excelchat Expert 17/07/2018 - 03:12
you are using google sheets right?
User 17/07/2018 - 03:12
because of the inbetween time
User 17/07/2018 - 03:12
nope excel 2016
User 17/07/2018 - 03:13
but the principle should be the same
Excelchat Expert 17/07/2018 - 03:13
yes
Excelchat Expert 17/07/2018 - 03:13
User 17/07/2018 - 03:16
Basically, every employee has different start dates and as a result their length of service differs. So I wanted to find a function, or set of nested functins that would help calculate the length of service alongside
Excelchat Expert 17/07/2018 - 03:18
ok the problem was not in the formula but the formatt
User 17/07/2018 - 03:18
if you are able to work it out and we run out of time, feel free to email me on: albrit@outlook.com
Excelchat Expert 17/07/2018 - 03:19
the way you stored the date and the salary information
Excelchat Expert 17/07/2018 - 03:19
so you actually typed £45,000.00
User 17/07/2018 - 03:19
yes
Excelchat Expert 17/07/2018 - 03:19
but instead you must type 45000
User 17/07/2018 - 03:19
ok
Excelchat Expert 17/07/2018 - 03:21
then go to more number format
User 17/07/2018 - 03:21
sure
User 17/07/2018 - 03:22
but excel recognises differnt format numbers
Excelchat Expert 17/07/2018 - 03:23
then select english (UK)
Excelchat Expert 17/07/2018 - 03:24
as soon as you type symbols excel treats that cell as text that was the reason you got the error
Excelchat Expert 17/07/2018 - 03:24
also the date atleast as i could see in the preview on the right was not in the correct format
User 17/07/2018 - 03:24
The problem is that I cannot express the correct fucntion or nested set of cuntions to deal with the between numbers, because 12 is larger than 3 and as a result I get the first set of results not and not the second set when the length of service is more than 12 months
Excelchat Expert 17/07/2018 - 03:25
oh..
Excelchat Expert 17/07/2018 - 03:25
1 min
Excelchat Expert 17/07/2018 - 03:26
oh in that case you have to go from largest to smallest...
Excelchat Expert 17/07/2018 - 03:27
you 2nd condition > 3 <12 months right so 1st we have to confirm it is less then 12
Excelchat Expert 17/07/2018 - 03:28
check Q now
Excelchat Expert 17/07/2018 - 03:28
you there
User 17/07/2018 - 03:29
yes
User 17/07/2018 - 03:29
I am
User 17/07/2018 - 03:29
I am jsut checking
User 17/07/2018 - 03:29
what if the length of service is less than 3 months
Excelchat Expert 17/07/2018 - 03:29
I was under the impression since i saw #value error here that was your issue
Excelchat Expert 17/07/2018 - 03:29
IFS(DATEDIF(D3,\$D\$1,"m")>12,E3/52*5,DATEDIF(D3,\$D\$1,"m")>3,E3/52*2,DATEDIF(D3,\$D\$1,"m")<3,0)
Excelchat Expert 17/07/2018 - 03:29
0
User 17/07/2018 - 03:29
would it work if you changes the date
User 17/07/2018 - 03:29
now
Excelchat Expert 17/07/2018 - 03:30
so for if we have to you the biggest net 1st and then the smaller ones
Excelchat Expert 17/07/2018 - 03:30
yes it will
User 17/07/2018 - 03:30
not
User 17/07/2018 - 03:30
so change the date in d 3
User 17/07/2018 - 03:30
to 2018
Excelchat Expert 17/07/2018 - 03:31
Excelchat Expert 17/07/2018 - 03:31
see
Excelchat Expert 17/07/2018 - 03:32
as you change the date value changes
User 17/07/2018 - 03:32
but if you cnage it to 2015 it should be bigger
Excelchat Expert 17/07/2018 - 03:33
q5:q8 has the values for our reference
Excelchat Expert 17/07/2018 - 03:33
changing D3
Excelchat Expert 17/07/2018 - 03:34
1 min let me enter the details
Excelchat Expert 17/07/2018 - 03:35
so as you see in Q the values are right
Excelchat Expert 17/07/2018 - 03:35
in case employment more than 12 months 5 weeks of pay
Excelchat Expert 17/07/2018 - 03:35
=E3/52*5
Excelchat Expert 17/07/2018 - 03:35
check Q9
Excelchat Expert 17/07/2018 - 03:36
and Q3 is the same so the correct formula is executed right
User 17/07/2018 - 03:36
So what was I missing only the order
User 17/07/2018 - 03:36
of things
Excelchat Expert 17/07/2018 - 03:36
yes
Excelchat Expert 17/07/2018 - 03:37
always remember the 1st if should be the one that you need to execute 1st so you must decide which to execute 1st
User 17/07/2018 - 03:38
Yes
User 17/07/2018 - 03:38
THnaks
User 17/07/2018 - 03:38
very much
Excelchat Expert 17/07/2018 - 03:38
so if your checking for less than x< a set of values
Excelchat Expert 17/07/2018 - 03:38
have the smallest 1st
Excelchat Expert 17/07/2018 - 03:38
welcome
Excelchat Expert 17/07/2018 - 03:39
Please do click on the end session and leave a feedback

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.