# Assistance request - IF statement and adding Time

[Closed]
Posts
2
Registration date
Tuesday March 6, 2018
Status
Member
Last seen
March 7, 2018
-
Posts
3
Registration date
Thursday August 31, 2017
Status
Member
Last seen
March 7, 2018
-
Hello Friends,

I would be extremely greatfull if someone would have the time to assist me with a formula in Excel. I also know this is a voluntary forum and everyone is busy so I will understand if you don't have the time to take away from your busy day to answer my question.

Here is what I am trying to do. I am trying to add time inside an IF statement, but I cannot get the time to show correctly when adding more than 30 mins.

Here is my formula....
=IF(\$F21=1,(\$C21+400),IF(\$F21=2,(\$C21+815),IF(\$F21=3,(\$C21+1230),IF(\$F21=4,(\$C21+1645),IF(\$F21=5,(\$C21+2100),0)))))

Basically
If cell F21 is a 1 then I want it to add 4hrs to C21,
if Cell F21 is a 2, then add 8 hrs and 15 mins to cell C21,
if Cell F21 is a 3, then add 12 hrs and 30 mins to C21,
if F21 is a 4 then add 16 hrs and 45 mins to C21,
if F21 is a 5 then add 21 hrs to C21.

In my example, C21 is 0630 and all works fine until F21 is a 3 and I need to add 16 hrs and 45 mins to 0630, it comes out as 1860 instead of 1900 (Military for 7pm). I also have the problem that if it goes past midnight it gives me 2730 instead of 0330.

Posts
3
Registration date
Thursday August 31, 2017
Status
Member
Last seen
March 7, 2018

1) The most obvious problem is your assumption that adding the number 1230 to a date/time cell is the same as adding the time 12:30 - What it actually does is to add 1230 days to the existing date/time.
2) Similarly the starting value in C21 should be shown as "06:30" and should be formatted by Excel as a time
3) Your formula could be expressed more concisely using Excel's CHOOSE function in this fashion:
`=C21+ CHOOSE(F21,4:00:00,8:15:00....)`
where the list of additions corresponds to the values of F21, i.e 1,2,3,4.....