Results 1 to 10 of 10
  1. #1
    New Lounger
    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)
    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.
    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_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.
    Last edited by leethanh1994; 2015-02-23 at 04:29.

  2. #2
    New Lounger
    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...

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 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.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 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,
    Maud
    Last edited by Maudibe; 2015-02-23 at 07:45.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 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
    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. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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; 2015-02-23 at 11:10.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    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 best
    Last edited by leethanh1994; 2015-02-23 at 12:46.

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 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

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 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

Posting Permissions

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