**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

just asked

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

[Uploaded an Excel file]

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)

[Uploaded an Excel file]

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

yes the answer is 0

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.*