Excel date function with formula not working

Hi Excel Experts,

Need your help to modify a formula for me as I am not able to make it work. File as attached. I'm currently having two problems.

Book1.xlsx


1) The problem i'm facing is that because i'm pulling data from column A from a tool, it shows the date as mm/dd/yyyy hh:mm:ss but when I select on the cell it shows as mm/dd/yyyy hh:mm:ss in the function bar.In column D I have an excel formula which only works correctly if I manually change the date in column A one by one and I have about 1200 records to fill in manually. I have tried to format cells and change it but it stil does not work. Is there any way to fix this?
I have two tables in the attached file. The bottom table works after I manually changed the dates in column A.

2) In column F, G and H I have a formula which works if the expiration date is today it will mark column F as Y else leave it blank and if expiring tomorrow then it should mark column G as Y as leave it blank and column H will be marked as Y if it is expiring more than 2 days time. Problem is whenever ticket is marked as Expired, column F(expiring today), will still be marked as Y and I am unable to remove it to leave it blank.
The bottom table in the attached file is an example if already expired today it still shows as Y in column F.

Column E is already set with a formula and that is the only one that works well so far. Hope someone can help me out in this.

Kind Regards,
Mark.

Originally Posted by nflsales

F14=IF(ISNUMBER(E14),IF(DATE(YEAR(D14),MONTH(D14),DAY(D14))=(TODAY()),"Y"," "),"")
Try this and copy towards down

Hi Siva,

Tried that code but if the time shows it is going to expire today, column F does not turn to Y. Is it possible to have a formula that if it is going to Expire today then column F should be in Y and if it has already Expired then column F should be blank?
Also appreciate if you have a solution for my first problem as well

Kind Regards,
Mark.

Originally Posted by nflsales

E14=IF(NOW()>D14,"Expired",TEXT(D14-NOW(),"dd hh:mm:ss"))
F14=IF(E14="Expired","",IF(DATE(YEAR(D14),MONTH(D14),DAY(D14))=(TODAY()),"Y"," "))
Try this

Hi Siva,

Yes that works wonderfully now.
Is there any fix for column D2-D6 so it can accept the date and time from column A2-A6 instead of me having to manually change the data one by one?

Kind Regards,
Mark.

Originally Posted by nflsales

In your attached sheet it is working problem
pls post a sample file with original data (Before entering manually)

Hi Siva,

Attached is the file.

BookTest.xlsx

Column A is data pulled directly from tool. When i paste in the Excel sheet, column D is showing the wrong date.

Kind Regards,
Mark.

Originally Posted by nflsales

It is showing correctly
Check your data in Column A from A4:A6, I think the year was 2015 instead of 2014

Hi Siva,

I have inserted now more columns. Maybe this will be clearer.

BookTest.xlsx

If you see data from A17 to A35, it should be month of January but D17 to D35 the months are wrong. Column A and D is set to dd-mm-yyyy h:mm:ss but the data is wrong in column D. Is the formula wrong?

Kind Regards,
Mark.

Originally Posted by nflsales

I think the problem with date format in regional settings
=TEXT(RIGHT(C2,8),"hh:mm:ss")+IF(ISTEXT(A2),A2,DATE(YEAR(A2),DAY(A2),MONTH(A2)))+LEFT(C2,2)
try this in D2 and copy towards down

Hi SIva,

Tried but it still did not work for column D17-D35. The dates and time are wrong and it does not show the minute and seconds.

Kind Regards,
Mark.

Hi Siva,

Any idea how to fix the time problem?

Kind Regards,
Mark.

Originally Posted by nflsales

sorry, i have not cross checked the formula

=TEXT(RIGHT(C2,8),"hh:mm:ss")+LEFT(C2,2)+IF(ISTEXT(A2),A2,DATE(YEAR(A2),DAY(A2),MONTH(A2))+MOD(A2,1))
try this in D2 and copy towards down

Hi Siva,

Many thanks this time it works although there is something a bit funny. If you noticed that there is some cells in column A as dd/mm/yyyy and some as dd-mm-yyyy. Whenever I select on the cell with the dd/mm/yyyy and click ENTER on the function bar, the values in cell D changes to a wrong value. For example if you select cells between A11-A16 then click on the formula bar and Enter, the value changes.
Sorry to keep bothering you on this but if there is nothing can be done about this then it's not really an issue but i'm finding this weird.

Kind Regards,
Mark.

Originally Posted by nflsales

I think you are importing data or copying Data in to Column from other source
The date format of the source from where you are copying and your system date formats are not same
i think on is DD/MM/YYYY and other is MM/DD/YYYY, so that you are facing the problem

Hi Siva,

Yes unfortunately thats how the data is copied directly from a tool. Nothing can be done there but your formula is now helping to save me a lot of time!!
Thank you once again Siva for helping me out.

Kind Regards,
Mark.

Originally Posted by bebo021999

I have no idea how the source treats the date from the year 2015, but Excel reading the date source with this logic: if day<=12 ==> read as month, else does not change.

Therefore, use a helper column (column I) to get the right date:

I17 = DATE(YEAR(A17),IF(DAY(A17)<=12,DAY(A17),MONTH(A17)),IF(DAY(A17)<=12,MONTH(A17),DAY(A17)))+MOD(A17,1)

Drag down then copy/paste special/value back to A17

Back to expired day, as per your calculation, E is based on hour while F is based on day, therefore, E is showing exprired while F is still in same day. Need to rebuilt as follow:

F1 = IF(INT($D2-NOW())=0,"Y","")
G1 = IF(INT($D2-NOW())=1,"Y","")
H1 = IF(INT($D2-NOW())=2,"Y","")

Drag down.

Hi Bebo,

Yes with the formula in column I now I can just copy and paste it back to column A. This is very very helpful and has saved me more time now. Really appreciate your help for this formula.

Kind Regards,
Mark.