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

1. ## 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)
The task above requires me to input a value, if this is a date, the answer is Yes. Otherwise, it is No.
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.
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_return5-1.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.

This is my last assignment of the course before holiday I hope I can get more replies...

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

4. 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,
Maud

5. 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
6-10 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

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

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

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

9. 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!

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•