Date format issue

formatting of a date and time should not make a difference

date and time are stored as numbers
Unless this is a TEXT entry and not a real date

change to general and it should show a number , and time as the decimal part

for example
44893.90363
is
28/11/2022 21:41:13

A sample sheet would help here

The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

From an outsider, programmer's perspective, I see nothing "wrong" with the spreadsheet. The dates in columns A and B are numbers as they should be, and the formula in column C is correctly calculating the difference between the date/times in A and B. As etaf says, number formatting should have no impact on the calculations.

The first incorrect (that you've labeled) entry (row 8) shows Excel correctly calculating the difference between 6 PM on 5 Jan and 7 PM on 5 Jun, which is correctly calculated (in hours) as 3600 hours. If I understand correctly, this should be seen as 6 PM on 1 May and 7 PM on 6 May, which would be a difference of 5 days.

Have you verified that all of the correct dates are really correct? For example, should the date in row 2 be 4 January or should it be 1 April (currently, it is 4 January)?

If there's something wrong in the spreadsheet, I suspect that it occurred during data entry/import, when incorrect date information was entered into Excel, so I expect that fixing this is going to be to change the data entry/import process so that the dates are entered correctly. How are you entering or importing this data into Excel? Where does it come from? What are your OS's regional settings (which tell Excel how to interpret date information)?

its not a format issue - is the way those dates are imported into excel and how excel has interpreted them

if you click on any of the cells and change format to general - you will see a number , thats how excel works on dates

So how is the file generated - do you import the dates

Because this data is not from our workplace we can not change entry process therefore I need to change format so it will give me correct number of hours difference from one location to another.

When people doing entries they are entring wrong format during entry process.

the format should be determined by the system date and time
Where are these people located ?


Excel will use one date format when imported based on the computer you are using

and it cannot tell the difference between MM/DD or DD/MM if they are valid dates

So if someone in say US is used to MM/DD format and choses to enter the date in a DD/MM system
then you get the error


IE
1st March 2020 - will be seen in an excel with a default format of DD/MM/YY as 1/3/20
BUT of the system date is MM/DD/YY
that will be seen as 1/3/20 - Which is 3rd jan 2020

January 5, 2022 June 5, 2022 which is wrong. The actual date is 1 May 2022 to 6 May 2022

5th Jan is 5/1 in DD/MM for mat
But if the system is MM/DD - then that will be seen as still 5/1 - but be 5th May

i swap spreadsheets on here all over the world and they choose the correct format
to have a mix like that - its how the info is entered

can you expand further on this

Your in Canada , and i understand that any format is allowed - YY/MM/DD , DD/MM/YY or MM/DD/YY

in UK systems are usually set to DD/MM/YY

If i have entered dates which can be correct even if MM/DD or DD/MM - ie not changed to text because
if i entered
25/12/22
that would be invalid as a MONTH in US format

It's a relatively simple process to "fix" this after data entry/import. Use the DATE() function coupled with YEAR(), MONTH(), and DAY() functions to "invert" the month and day -- =DATE(YEAR(A2),DAY(A2),MONTH(A2)). The hard part is being able to identify which dates have been incorrectly entered/imported. You have indicated that any difference greater than a week must indicate incorrect data entry/import. Is there more that needs to be considered when determining which values have been incorrectly entered?

For example, rows 58, 59, and 80 are labeled correct, but these dates are all in December. You indicated in post #5 that none of the dates should be in December, so these rows seem like they are incorrectly entered -- even though the difference seems correct. Perhaps the actual date doesn't matter to you, but it might. You will need to know whether or not these entries are acceptable or not.

I think the hardest part of this right now is figuring exactly how to determine which dates are correctly entered and which are not. I'm not sure that we on this side of the internet can tell you how to do that. You will need to carefully go through your data and identify the correct dates and incorrect dates and come to some clear understanding of how you determined which were incorrect and which were correct. Once you fully understand the logic needed, then we can distill that down to a series of if..then statements and program the logic into the spreadsheet using IF() functions. We need to rigorously understand the logic, though, before we can do the programming, and I doubt that we on this side of the internet understand your project in enough detail to come up with a rigorous logic that will detect correct and incorrect entries.

just to add to MrShorter as i have converted all the dates with C over 168 - BUT you have negatives in C and you say the format is MM/DD/YY - but the month is over 12
row13
row60
and other , i was just going through - but saw the reply

see column E in attached file
there are some errors (blank) because Arrival is bigger than Departure

Actual Arrival Time	Actual Departure Time	Result I want in hours (Diffrence Departure - Actual Hours)		Duration
27/06/2022 15:01	07/05/2022 21:05	-1217.9
28/09/2022 07:18	10/01/2022 04:08	-6267.2

When I did ISTEXT()/ISNUMBER() on your date, I did not see any text entries among your dates, so the ISNUMBER() test looks to me like it will always return TRUE, and this version will always switch month and day -- even for those entries you have labeled correct. Is that what you want to do?

As a guess, I tried "assuming any date less than the 12th of the month incorrectly switched month and day" logic =IF(DAY(A2)<=12,DATE(YEAR(A2),DAY(A2),MONTH(A2))+MOD(A2,1),A2) [copy/paste/fill into K2:L100], then take the difference of those columns, I get reasonable results for the sample -- except for row 35 that is still calculating a time longer than 1 week. Your current file has row 35 departing in May before it arrives in June. My correction claims it was there from 27 Jun to 5 July which is longer than a week. How hard is the "never longer than a week" criteria that you've indicated? Is it possible for these times to occasionally be longer than a week?

Anyway, that's just a guess. I don't really know what you should do here.

with Power Query my friend

see Data tab - Show Queries - dbl click on table on the right side

if Dept-Arrival is > 7 or < 0 then swap day with month else continue Dept-Arrival

what kind of formatting you want to change?

I did for both column of dates
if Dept-Arrival is > 7 or < 0 then swap day with month else continue Dept-Arrival
and you can see result

but I've never heard of month number 28

If I follow the logic sandy666 used, it was a simple "any time the difference is larger than a week, assume both dates are entered incorrectly and switch month/day for both dates." That logic seems to work for most -- at least as far as getting the correct difference. I'm not sure if it catches the errors like row 58 where the dates are both incorrect but the difference just happens to be correct (because they occur on the same day, so the date information drops out of the subtraction).

Did you try my simplistic "assume all dates before the 12th of the month were incorrectly entered" logic (I'm confident sandy666 could translate that into power query if you would prefer power query)? As I noted, that seemed to work for all entries assuming that row 35 is correct even though it appears to be longer than a week.

As I said, the difficult part is the logic of deciding which dates are correct and which are incorrect -- no matter what programming language (spreadsheet formula or power query or other) you use to perform the task. How do you determine which dates are incorrectly entered, and, from there, how do you determine which date should have been entered?

in your example you've two reasons of negative values: day/month and Arrival>Departure so you can define if statement for these two but (there's always a "but") in case

27/06/2022 15:01

07/05/2022 21:05

both dates are proper, you cannot swap day/month and Departure - Arrival give you negative value. You can try the trick swap dates (second statement of IF)

07/05/2022 21:05

27/06/2022 15:01

but it will be cheating so you have to manually correct it

there is no golden solution , sorry
Unfortunately, the weakest part is the human

EDIT:
anyway easier is correct a few errors than 3/4 of table with 1000 rows long

You are welcome

you can use Data Validation to check if right date is bigger than left date and block improper manual entry (with copy/paste it doesn't work!)

so if the problem is solved, hit Add Reputation (bottom left corner next to the post that was helpful)
and then mark the thread as SOLVED (top above your first post - Thread Tools)

common (non-power query) strategies for converting numbers stored as text to numbers: https://support.microsoft.com/en-us/...1-c5bad0f0a885 I used Text to Columns on your data (specifying MDY dates on step 3 of the text import wizard), and seemed to have no trouble converting the text to numbers.

I don't know this is better or not but check this and "repair" improper dates

btw. attaching file read carefully big yellow banner at the top of this page and try to use it in Advanced mode

this is not a solution, I show you what and where you need to fix with dates
Solution is contained in the previous example file

eg.

05/12/2022 03:12

13/05/2022 02:28

Arrival is bigger than Departure

###########

Arrival is bigger than Departure but require swap day/month, however some of the Arrivals are really bigger

31/03/2022 15:04

04/01/2022 02:15

Arrival is bigger than Departure

###########

if you want to use Power Query forget about Excel features and functions (sometimes they work sometimes not)
as you can see all dates are correct, doesn't matter they are in DMY or MDY format, bigger than other or not
here is solution for that:

Please Login or Register  to view this content.

not text2columns

I did but some converting into date and time but some are in String.

Sorry I'm a bit late here, but what exactly did you do when you tried Text to Columns? I didn't have any trouble converting the strings to numbers. What I see in your file between post 24 (all text) and post 26 (some text some number) is:

1) As I noted in post 25, all of the text dates appear to be MDY dates. At step 3 of the text import wizard, I very carefully told Excel to use MDY standards to interpret the dates (and maybe because my US-centric OS settings are set to MDY), and Excel appeared to correctly convert all dates to numbers with no problems.
2) When I look at your partially converted file in post 26, I notice that those dates that are converted to numbers appear to have been interpreted as DMY dates. Row 5 that I and my MDY settings interpreted as 1 April 2022, your file shows as 4 January 2022.
3) Assuming that Excel was trying to use a DMY setting to interpret the dates, those dates that are not converted to numbers have no meaning as DMY dates (there is no 17th month or 31st month or ...).

I will ask again, exactly how are you converting these strings to numbers/dates -- specifically are you doing everything you can to make sure that Excel will see that these start out as MDY dates so that Excel will not try to interpret these as DMY dates. It's beginning to appear to me that the most of the problem (if not all of the problem) appears to be related to Excel mistakenly reading text dates as DMY dates and not recognizing them as MDY dates. If we can figure out how to get the date entry/import process to correctly read in the dates, I suspect that most if not all of the other problems are going to go away.

see this but I hope you will do everything yourself

worth to read especially cyan and grey lines of the first table

You are welcome

you've some "errors" in source data, like (dd/mm/yyyy hh:mm:ss PM) or AM), check your own file

sure ............

btw. from 05 January to 05 June is not a five days with dd/mm/yyyy format of date

05/01/2022 18:05

05/06/2022 19:16

so your whole source table is in MM/DD/YYYY format ?

could you attach original source file without any text2columns etc etc ? will be best if you set TEXT format to these two columns

you need to eat something from healthy food to calm down

sorry I was late, could attach this file again but set these two columns to TEXT format ?

Happy Birthday to You

in progress

if you see in Duration column something like ######## in the green table filter Duration by this and take screenshot of this (all columns) , enough only lines as you see on the screen

I asked about screenshot not a word

like this but I need yours
screenshot.png

Originally Posted by sandy666

in progress

if you see in Duration column something like ######## in the green table filter Duration by this and take screenshot of this (all columns) , enough only lines as you see on the screen

my dear friend, could you read the posts more carefully ?

if there is no ##### filter duration by <0 and take a screenshot from the result, green table

shit! shit! shit! someone sick in the head produced these dates!

date like this 24/11/2022 07:25 will (should) change to your MDY format automatically but with date like this 01/04/2022 12:40 system thinking everything is OK
because system knows the first place cannot be bigger than 12 so it will swap first and second place to MDY

a drowning man will clutch at a straw , try this
- open brand new excel file
- copy these three columns from old file and paste as value (or paste as text) to this new
- save it and try again

I didn't use Get Data but From Table (Alt+A+PT from keyboard) or used trick with Named Range
Get Data is in 365 but as you can see I am NOT on 365 )

ok, here is - not ideal (last time)
you need to try yourself because our system dates are different and additionally human errors exists (most of them) or butterfly effect

After a couple of pages of Power Query discussion, I will tentatively venture back into the discussion. It still appears to me that the entire problem is that sometimes Power Query is reading a text date as a DMY date when it should be reading as MDY. If I go back to your file in post #24 where all of the entries appear to be "date/time stored as text" and compare, it looks to me like all of the text dates are mm/dd/yyy hh:mm (MDY date with 24 hour clock). If we can get whatever utility we use to read the text to recognize that the text dates are MDY dates, everything should work just fine.

As I noted back in post #25, I used Text to Columns to read the text data -- carefully specifying that Excel should read the text as MDY dates at step 3 -- and had no trouble converting the text to numbers. I can then handle the missing time stamps as part of the formula in column C [maybe something like =IF(COUNT(A2:B2)<2,"input missing",B2-A2)]. I do not recall any feedback from you on this approach, so I don't know if you had difficulty using Text to Columns, since the discussion from there seemed to focus exclusively on Power Query approaches.

Since then, it seems that you have preferred the Power Query approach, which should be fine. I cannot help because I do not know how to tell Power Query to "read these text strings as MDY dates and never read them as DMY dates." I assume that Power Query knows how to do that, so I will leave it to those who know Power Query to explain where the "read MDY never DMY" command is. Maybe my purpose is to suggest again that, if you are not required to use Power Query, the old fashioned Text to Columns command might be easier.

generally speaking the problem is

ActualArrivalTime	ActualDepartureTime		should be	dd/mm/yyyy
31/03/2022 15:04	04/01/2022 02:15			01/04/2022 02:15
05/12/2022 03:12	13/05/2022 02:28		12/05/2022 03:12
27/06/2022 15:01	07/05/2022 21:05			05/07/2022 21:05
09/12/2022 16:22	14/09/2022 09:33		12/09/2022 16:22
28/09/2022 07:18	10/01/2022 04:08			01/10/2022 04:08
10/12/2022 17:49	13/10/2022 09:37		12/10/2022 17:49
11/12/2022 01:11	16/11/2022 02:32		12/11/2022 01:11
28/04/2022 03:17	05/02/2022 09:34			02/05/2022 09:34
30/05/2022 15:38	06/01/2022 09:45			01/06/2022 09:45

and so on up to 641 rows

T2C doesn't work, PQ Culture-US/CA doesn't work

so I filtered out ca. 2000 records (duplicates/zeroes/less than zero)
I could swap day with month and vice versa but the dates are mixed DMY and MDY so it doesn't make sense

Yeah, I know that
I'll wait for MrShorty maybe he can enlighten us on how to fix messed up date formats

01/04/2022 and 04/01/2022 this is the correct date format and T2C or PQ Culture/US/CA don't want to change it

IMHO golden solution for this is use the same date time format everywhere (Revolution!!! )

btw. your file still contains 605 improper duration records

Having a mix of MDY and DMY dates is definitely a problem. I don't know if I have any "magic" fixes. Comparing the file in post #90 with the file in post #24 and I notice that the file in post #90 has a mix of MDY and DMY dates, but the file in post #24 has all MDY dates.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I would be asking myself where the data for post #24 came from and where the data for post #90 came from and then trying to figure out how I can always get the data from the same source that produced the data in post #24.

I did find a few dozen problems in the post #24 data. There are a few dozen timestamps with added parentheses and the time is in a 12 hour clock format rather than a 24 hour clock format. Find/Replace (Find what? extraneous character Replace with? nothing) can easily deal with the parentheses and the morning time stamps. The afternoon time stamps would require a little more work, but should be manageable. If I were going back to the source of the data, in addition to how to always get all MDY dates, I might also ask if there is any way to always get all 24 hour clock timestamps.

problem is with DMY and MDY , the rest is cosmetic

AMJADMemon's hard work awaits you, manually correcting over twenty-four thousand dates

What I am saying is that, as far as I can tell, the data in post #24 has no DMY dates, they are all MDY. I agree that a mix of DMY and MDY is a problem, and the solution is to get all one or the other. In post #24, it seems that we briefly had an all MDY data set.

Yes, Post 24 I corrected the data once realize.

What do you mean that you corrected the data in post #24? My impression looking at the file in post #24 is that it is ready to be processed as all MDY dates with a few problem entries due to a 12 hour clock. Whatever you did to get the file in post #24 seems like the best way forward.

Hi Sandy,

I am not sure why few of the entries which does have more than 30 days calculating wrong duration. One duration column is coming from system and one I created on Power Query which you helped me earlier. I am not quite sure why red entries calculating wrong. I am not sure how to attached file on direct msg.

Thanks