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.
