Computation of time and dates

Hi, follow attached.

Cheers!

@jomaor1

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

Hi,

I altered your formula on Column H thinking the Diff was just Release - Research, apologies.
But the formula on Column I should be right, as it ads 30min for each day on Column H.
I'm just having a hard time understanding what you need on Column H. It makes zero sense to me

The following formulas produce the same values as those that were originally placed in columns H:I.
For column H: =COUNTIFS(C$3:C$16,C3)
For column I: =IF(H3=1,D3,D3+H3*TIME(0,30,0))
Let us know if you have any questions.

The "11:00" values in column K are text values.
Modify the formula for column K to read: =IF(COUNTIFS(C$3:C$16,C3)=1,"11:00"+0,"11:00"+(TIME(0,30,0)*COUNTIFS(C$3:C$16,C3)))
Likewise you could modify the formula in column P to read: =IF(D3>(IF(COUNTIFS(C$3:C$16,C3)=1,"11:00"+0,"11:00"+(TIME(0,30,0)*COUNTIFS(C$3:C$16,C3)))),"Yes","No")
Let us know if you have any questions.

Originally Posted by angelosison00

im surprise that all i need to do is to add +0 in my formula.

"11:00" is a string. But when a string is used in an arithmetic expression (e.g. "11:00" + 0) and Excel can interpret the string as a number, Excel converts the string to its numeric value, just as Excel does when we type the text into a cell that is formatted as General.

Any arithmetic operation does the same thing. Typically, I write --"11:00". Some people mistakenly think that "--" is an operator, and that "--" is necessary for the conversion to happen. But there is nothing sacrosanct about "--". It is just two unary minuses in a row, which results in the same numeric value.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.