Assistance request - IF statement and adding Time

Closed
msfrandson Posts 2 Registration date Tuesday March 6, 2018 Status Member Last seen March 7, 2018 - Mar 7, 2018 at 12:59 PM
hershmab Posts 3 Registration date Thursday August 31, 2017 Status Member Last seen March 7, 2018 - Mar 7, 2018 at 01:54 PM
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.

Can you Please help me with this? I have not had luck searching for the answer on Google so any assistance would be greatly appreciated.


Related:

1 response

hershmab Posts 3 Registration date Thursday August 31, 2017 Status Member Last seen March 7, 2018
Mar 7, 2018 at 01:54 PM
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.....
0