Formula time as text

[sjvenz]

I have the folowing formula


=IF(K21=1,800,IF(K21<1600,K21+800,IF(K21=1600,1,IF(K21>1600,K21-1600))))

What it does is looks at the cell and adds what the value is + 800 which is 8hrs


The problem is that now the powers that be want this addition now to be 8hrs 45mins.

Using the above formula and changing it to


=IF(K21=1,845,IF(K21<1600,K21+845,IF(K21=1600,45,IF(K21>1600,K21-1645))))

This work only on some values

1 = 0845
1600 = 0045

If 0820 is entered then the end result is 1665 when it should be 1705


Does anyone have any ideas about who to get around the 2400 period

[TonyJollans]

Why don't you use proper Time values? Then Excel could take care of the details.

If you must use the values as they are, you can make your original formula simpler, or you can make a more general formula which should cope with all values:


=IF(K21=1,845,MOD(K21+845+40*(MOD(K21,100)+MOD(845,100)>60),2400))

[TonyJollans]

Or, slightly simpler, I think ..


=MOD(K21*(K21<>1)+845+40*(MOD(K21,100)+MOD(845,100)>60),2400)

[sjvenz]

The change works well but if I enter 0915 then 1760 is returned and not 1800

[sjvenz]

I think I used to wrong reply I used the Quick Reply


he change works well but if I enter 0915 then 1760 is returned and not 1800

[sjvenz]

And this is the full formula being used


=IF(ISERROR(SEARCH("DOO",RC[1])),IF(ISERROR(SEARCH("8HRS",RC[1])),IF(ISERROR(SEARCH("10HRS",RC[1])),IF(RC[-1]="","",IF(RC[-1]=1,1100,IF(RC[-1]<1300,RC[-1]+1100,IF(RC[-1]=1300,1,IF(RC[-1]>1300,RC[-1]-1300))))),IF(RC[-1]="","",IF(RC[-1]=1,1000,IF(RC[-1]<1400,RC[-1]+1000,IF(RC[-1]=1400,1,IF(RC[-1]>1400,RC[-1]-1400)))))),IF(RC[-1]="","",IF(RC[-1]=1,800,IF(RC[-1]<1600,RC[-1]+800,IF(RC[-1]=1600,1,IF(RC[-1]>1600,RC[-1]-1600)))))),MOD(RC[-1]*(RC[-1]<>1)+845+40*(MOD(RC[-1],100)+MOD(845,100)>60),2400))

[sjvenz]

I changed >60 to >50 and all work well