IF statement and adding Military Time Question [Closed]

msfrandson 2 Posts Tuesday March 6, 2018Registration date March 7, 2018 Last seen - Mar 6, 2018 at 06:58 PM - Latest reply: Ambucias 52674 Posts Monday February 1, 2010Registration dateModeratorStatus June 25, 2018 Last seen
- Mar 7, 2018 at 05:12 AM
Hello,

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 help?
See more 

1 reply

Ambucias 52674 Posts Monday February 1, 2010Registration dateModeratorStatus June 25, 2018 Last seen - Mar 7, 2018 at 05:12 AM
0
Thank you
Greetings,

Please, bare in mind that CCM is a mutual aid community, all answers on the forum are provided by volunteers who give their time free to help solve issues.

Hence we request that CCM forum users show mutual respect. Therefore, when asking for assistance, please be courteous, as for elsewhere in similar circumstances in every day society when asking for help. (Usually, how would someone ask for help? i.e. please, thank you, appreciate, grateful, etc.)

Being courteous will increase your chances of getting the help you need.

http://ccm.net/contents/948-terms-of-use-for-ccm-respect-for-others

Please, copy, modify and repost your message respecting the politeness charter.

We trust that you understand.

Moderator