Results 1 to 10 of 10
Thread: If function with input in form of dates and texts, use if function to calculate daily allowance

20150223, 04:26 #1
 Join Date
 Feb 2015
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
If function with input in form of dates and texts, use if function to calculate daily allowance
Hello everyone,
I'm a newbie who just know the forum...
I have an exercise about if function in excel. Honestly, I did pretty well the previous assignments but this one is much complicated...
Please give me some instructions for this, thank you all a lot:
Task 1: Use Excel functions to check whether
the input in K16 is a date. The result is seen on L16 (the cell next to the inputted one)
It's difficult for me to help If function identify the form of inputted data being date and text .
I have tried many ways such as:
_(If K1=datevalue(1.1.1990); "Yes"; "No")
_(If K1=datevalue(dd.mm.yy); "Yes"; "No")
_(If K1=date; "Yes"; "No")
...
They are all wrong.
Task 2:
I have to calculate daily allowance of a journey based on the given dates + times.
I will upload my file here in order to make it straightforward.
Excel_return51.xlsx
I really don't know whether i should use If function in this case 'cause it's much hard.
If hours = 48, so the payment is doubled... So what formula I should use...
Please give me some instructions, thank you and wish you all a good week.Last edited by leethanh1994; 20150223 at 04:29.

20150223, 06:04 #2
 Join Date
 Feb 2015
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
PLease help me...
This is my last assignment of the course before holiday I hope I can get more replies...

20150223, 06:47 #3
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,091
 Thanks
 73
 Thanked 124 Times in 109 Posts
I am not going to do your assignment for you, but here's a suggestion for Task 1:
Look up the DATEVALUE function and see what it returns if the argument is, or isn't, a date.
Then think about how you can use that result to answer the question you have been given.

20150223, 07:26 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,882
 Thanks
 147
 Thanked 732 Times in 664 Posts
Leethanh,
Th should do what you want. in cell L16, place the following formula to evaluate K16 formatted as text.
=IF(IFERROR(DATEVALUE(K16),0)>0,"Yes","No")
HTH,
MaudLast edited by Maudibe; 20150223 at 07:45.

20150223, 08:23 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,882
 Thanks
 147
 Thanked 732 Times in 664 Posts
Leethanh,
Task 2 formula in cell G3 would be:
=IF(AND(F3>=3,F3<=10),18,IF(F3<26,40,IF(F3<30,58,I F(F3>=30,80))))
Only if I am interpreting the scale correctly
610 hours= 18 Allowance
>10<26 hours= 40 Allowance
>=26<30 hours= 58 Allowance
>=30 hours= 80 Allowance
Sounds like you still need to validate that all the data is in the correct format and the user is notified with a descriptive error message if applicable.
HTH,
Maud

20150223, 10:19 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,864
 Thanks
 416
 Thanked 1,576 Times in 1,427 Posts
Hey Y'all,
Like Martin I don't think we should provide solutions for students but rather guide them to find the answer. That said, I can't seem to get this formula: =IF(IFERROR(DATEVALUE(K16),0)>0,"Yes","No") to work properly? Am I missing something?
TestDate.JPG
However, as the heading to col D suggests use of the Cell function in a formula should lead to a workable solution.
HTH
Edit, I made an error initially in the reference in the Col C formula, my bad! It's fixed thanks to Rory and the image above shows the new results where the correct value is returned in C4.Last edited by RetiredGeek; 20150223 at 11:10.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150223, 12:41 #7
 Join Date
 Feb 2015
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
So great !!!
I will consider them all and create one for my own, ofc i will not copy and paste because i have to explain to my teacher.
I have found the formula for the task 1, here it is:=IF(AND((K16>=0),(K16<=2958465)),"YES","NO")
I think it works well, and the task 2 is still in a mess...
Now i will solve it.
Thank you all a great deal for these nice instructions.
Wish you all the bestLast edited by leethanh1994; 20150223 at 12:46.

20150223, 13:01 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,882
 Thanks
 147
 Thanked 732 Times in 664 Posts
RG,
The cell with the date to be checked must be formatted as text as I indicated in bold.
I think the solution provided would be no different than finding elsewhere on the internet or consulting a friend for the answer. As leethanh points out, "I have to explain to my teacher", so she will still need to digest and understand it

20150223, 13:34 #9
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,864
 Thanks
 416
 Thanked 1,576 Times in 1,427 Posts
Maud,
True enough. However, entering dates as text is not a usual procedure which is why I looked for another solution. As you say she has to explain it (posted after my post) so I'll show the formula I used here:
=IF(LEFT(CELL("Format",B2),1)="D","Yes","No")
As always that CAT get's skinned in a variety of ways!May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150223, 17:55 #10
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,882
 Thanks
 147
 Thanked 732 Times in 664 Posts
RG,
I had toyed with cell format but I found it to be buggy. In the cell with the formula, if you typed a string with the cell formatted as a date or typed a date in a cell formatted as text, the formula would no longer work unless you performed a manual recalc