ODD DATE & TIME (FORMAT) CALCULATION

[CeeGee96]

Greetings lifesaving gurus

It has been a long time since I’ve been here and/or done anything exciting with Excel, and I’ve forgotten quite a lot ☹ However I know you guys & gals are unmatchable, therefore: help please!

At work we use an uncommon format for the date and time of DDHHMM and month MMM (known as a DTG) It is standard in our area of work, but probable not much elsewhere and as Excel doesn’t know what a DTG is I use hidden formula to build it into something Excel will recognise.

My problem stems from calculating the days, hours and minutes between date/times, despite Excel recognising a “constructed” date and time i.e. dd/mm/yy hh:mm I cannot get the formula(s) I’ve tried to calculate correctly for every line of data;

As an example, it is okay with 02/02/23 10:17 to 02/02/23 10:27 It knows that is 10 minutes. However, it doesn’t like 02/02/23 09:18 to 05/02/23 08:00 which it seems to think is 1095 days, 22 hours and 42 minutes apart.

And it certainly doesn’t like calculating from 24/02/23 09:10 to 12/03/23 09:17

My system will not let me upload the spreadsheet as an example however I will include a screenshot, and what formula I am trying at the moment

Finally, much as I like Macro, I cannot use them in this project as work are paranoid about the vulnerability of coding (sadly).



FORMULA BEING TRIED:

(column) P formula :

INT(V18-U18)&" days "&TEXT(V18-U18,"h"" hrs ""m"" mins """)

(column) S:

MONTH(B18)

(column) U:

TEXT((DATE($A$2,$S18,LEFT($N18,2))),"dd/mm/yy")&" "&TEXT((TIME(MID($N18,3,2),RIGHT($N18,2),0)),"h:mm")

(column) V:

IF(LEFT(O18,2)<LEFT(N18,2),TEXT((DATE($A$2,($S18+1),LEFT($O18,2))),"dd/mm/yy")&" "&TEXT((TIME(MID($O18,3,2),RIGHT($O18,2),0)),"h:mm"),TEXT((DATE($A$2,$S18,LEFT($O18,2))),"dd/mm/yy")&" "&TEXT((TIME(MID($O18,3,2),RIGHT($O18,2),0)),"h:mm"))



Thank you in anticipation!

 

[jasonb75]

Are the examples in your first screen capture accurate? Based on your description I'm not seeing anything in the DTG column that tells us which month is referred to which is going to be a necessity looking at the first two rows.

 

[Bosquedeguate]

did you try using the =DATEDIF() function instead of =INT(V18-U18)? Also, as a test create two adjacent cells, copy the contents of Cells V18, and U18 and format the value as a number to make sure the two serial date values make sense.

 

[jasonb75]

I thought I was missing something simple so came back and had another look. See if this does what you need it to (based on the second screen capture in your original post).

Book1
	B												N	O	P	Q	R	S
18	02/01/2023												021500	011400			02/01/23 15:00	01/01/23 14:00
19	02/02/2023												020918	050800	2 Days, 22 hrs 42 mins		02/02/23 09:18	05/02/23 08:00
20	02/02/2023												021017	021027	0 Days, 0 hrs 10 mins		02/02/23 10:17	02/02/23 10:27
21	02/02/2023												021032	021038	0 Days, 0 hrs 6 mins		02/02/23 10:32	02/02/23 10:38
22	02/02/2023												021045	021616	0 Days, 5 hrs 31 mins		02/02/23 10:45	02/02/23 16:16
Sheet1

Cell Formulas
Range	Formula
R18:S22	R18	=IFERROR(EOMONTH($B18,-1)+LEFT(N18,2)+REPLACE(RIGHT(N18,4),3,0,":"),"")
P19:P22	P19	=IFERROR(INT(S19-R19) &" Days, "&TEXT(S19-R19,"h"" hrs ""m"" mins """),"")

 

[CeeGee96]

Are the examples in your first screen capture accurate? Based on your description I'm not seeing anything in the DTG column that tells us which month is referred to which is going to be a necessity looking at the first two rows.

sorry, ignore the first screen shot, I did upload it and then browser crashed, when I reposted it wasnt there until I submitted the second time

 

[CeeGee96]

did you try using the =DATEDIF() function instead of =INT(V18-U18)? Also, as a test create two adjacent cells, copy the contents of Cells V18, and U18 and format the value as a number to make sure the two serial date values make sense.

I have tried datedif. ALos various formats, incuding the standard offering and custom ones

 

[CeeGee96]

I thought I was missing something simple so came back and had another look. See if this does what you need it to (based on the second screen capture in your original post).

Book1
	B												N	O	P	Q	R	S
18	02/01/2023												021500	011400			02/01/23 15:00	01/01/23 14:00
19	02/02/2023												020918	050800	2 Days, 22 hrs 42 mins		02/02/23 09:18	05/02/23 08:00
20	02/02/2023												021017	021027	0 Days, 0 hrs 10 mins		02/02/23 10:17	02/02/23 10:27
21	02/02/2023												021032	021038	0 Days, 0 hrs 6 mins		02/02/23 10:32	02/02/23 10:38
22	02/02/2023												021045	021616	0 Days, 5 hrs 31 mins		02/02/23 10:45	02/02/23 16:16
Sheet1

Cell Formulas
Range	Formula
R18:S22	R18	=IFERROR(EOMONTH($B18,-1)+LEFT(N18,2)+REPLACE(RIGHT(N18,4),3,0,":"),"")
P19:P22	P19	=IFERROR(INT(S19-R19) &" Days, "&TEXT(S19-R19,"h"" hrs ""m"" mins """),"")

This works unless the end date/time is in the next month, but I thik I can look at and hopefully solve that one, thank you Jasonb 75 it has given me a solution, hopefully.

 

[jasonb75]

How would we identify that from the data that we have to work with? I couldn't see anything in the samples that shows if it is a different month.

 

[Tetra201]

@ CeeGee96: Try using the following formulas in Columns U and V, respectively:

=DATE($A$2,MONTH($B18),LEFT($N18,2))+REPLACE(RIGHT($N18,4),3,0,":")
=DATE($A$2,MONTH($B18)+(LEFT($O18,2)<LEFT($N18,2)),LEFT($O18,2))+REPLACE(RIGHT($O18,4),3,0,":")