If formula to show results based on the number of days between two dates

Hi,

I need help on the IF formula to show results based on the number of days between two dates using multiple conditions.

For Ex:

If col A has date 11/24/2020 and Col B has same date 11/24/2020 then the column C should reflect "T+0"
If col A has date 11/24/2020 and Col B has same date 11/25/2020 then the column C should reflect :"T+1"
If col A has date 11/24/2020 and Col B has same date 11/26/2020 then the column C should reflect :"T+2"

and so on..

How can I do this using IF formula.

Appreciate your assistance.

Thanks,
Arun

Assuming your dates start in A2, allowing for a header row, then you could have this formula in C2:

=IF(OR(A2="",B2=""),"","T+"&(B2-A2))

Copy down as required.

Hope this helps.

Pete

Hi Pete,

Thanks for your assistance. This formula works as per my expectation.
Just one problem, the result is showing as "T+0.549421296294895", I am not sure how to fix this? Can you help.

In my excel, the dates in column A & B is in this format "2/11/2020 1:43:03 AM", is this a reason why my result is showing in fraction values?

Can you help to fix this issue please.

Thanks,
Sakthi

Yes, times in Excel are stored internally as fractions of a 24-hour day. You can solve the problem by changing the formula to this in C2:

=IF(OR(A2="",B2=""),"","T+"&(INT(B2)-INT(A2)))

Hope this helps.

Pete

Excellent. This new formula solved my problem.
Thank you very much for your assistance Pete.

One last question. This formula works fine if the date is in US format (MM/DD/YYYY), however if I enter the date in UK format (DD/MM/YYYY) it calculates the number of days incorrectly.

For example:

US date format(MM/DD/YYYY) :col A has date 02/11/2020 and Col B has date 02/12/2020 and column C is reflecting as "T+1". This is as expected and works fine.
UK date format(DD/MM/YYYY) :col A has date 02/11/2020 and Col B has date 03/11/2020 and column C is reflecting as "T+29". this is not correct.

Can you help to fix this formula to work for all date formats.

Thank you,
Arun

You will need to change your regional settings to UK format before entering UK-style dates, and then back again to US format if you want to enter some dates in that style.

Alternatively, you could use another column to tell Excel which format you are using for that date (e.g. by entering UK or US), and then another formula could transfer the dates into the appropriate value, and the formula that I gave you earlier would need to take the dates from the new columns.

It would be easier just to stick to one format. If the dates are coming from a file, then you should manipulate them within that file before pasting them in.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete