'IF' statement not working with time data

[H28Sailor]

Hi

I have column A - time data e.g. 12:00:00
Column B - time buckets formula = A2 - MOD(A2, 40/24/60) which creates 40 minute buckets

Now for two of them, and just two of them, I wish to create 30 minute buckets i.e. using A2-MOD(A2, 30/24/60)

I have tried something like the following but excel is not happy

=IF((A2>=12:00:00) AND (A2<12:40:00), A2-MOD(A2, 30/24/60), IF((A2>=16:00:00) AND (A2<16:40:00), A2-MOD(A2, 30/24/60), A2-MOD, 40/24/60))

Help

Bob M

p.s. I am not worried about the odd bits created by shrinking 40 minutes down to 30 minutes

[FDibbins]

Looks likje you were almost there

Try this...
=IF(AND(A2>=12:00:00,A2<12:40:00,A2>=16:00:00,A2<16:40:00), A2-MOD(A2, 30/24/60), A2-MOD(A2, 40/24/60))

(you had a problem with the AND, not the IF lol)

[H28Sailor]

Hi there

Have tried the above formula but Excel is still unhappy

[FDibbins]

OK do you have a sample for me to look at? Show what you are working with, a few expected answers and how you got them (if needed)

[H28Sailor]

Hi again

what is wrong with the current suggested formula is that the AND is being used for the two different time buckets

we need an OR between the 12:00 to 12:40 period and the 16:00 to 16:40 period

does this make sense?

Bob M

[FDibbins]

OK Im just guessing now, although the way I put the AND() together, it should work for both time frames

=IF(or(AND(A2>=12:00:00,A2<12:40:00),and(A2>=16:00:00,A2<16:40:00)), A2-MOD(A2, 30/24/60), A2-MOD(A2, 40/24/60))

If that doesnt work, upload a sample workbook

[H28Sailor]

The above looks OK to me but still Excel is not happy

you need the OR !

here is some data

12:29:58.306 12:30:00
12:29:58.406 12:30:00
12:29:58.407 12:30:00
12:29:58.758 12:30:00
12:30:01.606 12:40:00
12:30:01.708 12:40:00
12:30:01.709 12:40:00
12:30:01.710 12:40:00

Bob M

[martindwilson]

the time needs to be in quotes
=IF(OR(AND(A10>="12:00:00",A10<"12:40:00"),AND(A10>="16:00:00",A10<"16:40:00")),A10-MOD(A10, 30/24/60), A10-MOD(A10, 40/24/60))

[H28Sailor]

Thank you

Exactly what I wanted

Bob M

[FDibbins]

Thanks for the assist Martin

[martindwilson]

hm it had problems but this is better
=IF(OR(AND(A2>=VALUE("12:00:00"),B2<VALUE("12:40:00")),AND(A2>=VALUE("16:00:00"),B2<VALUE("16:40:00"))),(A2-MOD(A2, 40/24/60)),(A2-MOD(A2,30/24/60)))

[H28Sailor]

Hi again

I spoke too soon

Martin Wilson suggested the following:-

hm it had problems but this is better
=IF(OR(AND(A2>=VALUE("12:00:00"),B2<VALUE("12:40:00")),AND(A2>=VALUE("16:00:00"),B2<VALUE("16:40:00"))),(A2-MOD(A2, 40/24/60)),(A2-MOD(A2,30/24/60)))

The 40 and the 30 need reversing but I have tried this on a full set of data and......................

The formula is producing 30 minute time buckets at times other than 12:00 - 12:39 and 16:00 thru 16:39

How odd?

So still not what I am seeking

Bob M

[martindwilson]

lets try again with just say 10 time values of showing the results expected for each,in an excel work book
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[H28Sailor]

Hi

I am afraid a sample of a dozen records does not really show the end result

What I am doing currently:-

Using the formula =A2-MOD(A2, 40/24/60) for all 1 million records say

I then manually locate the beginning of my two special periods i.e. 12:00:00 and 16:00:00 for each day and manually change the formula to A2-MOD(A2, 36/24/60)

When I use the much longer all embracing formula - it does the two special periods correctly BUT also changes other periods - seemingly randomly

Bob M

[martindwilson]

whet ones dont work then? just post some that do and some that don't from your real data

[FDibbins]

You have been asked for a sample file three times already. If you cant/dont want to provide 1, Im not guessing any more.