time calculations

[atchorizon]

Good day all … Merry X-mas and Happy new year..
If anybody can help me here,,, I have a system that generates various information with time stamps. The time stamps formatted as text and it’s DD/HHMM, HHMM is in 24 hours format. (I.e. 12/1545 that is quarter to four on the 12th of the month).
I want to do some further time calculation. For example, I want to add and subtract times and dates, calculate elapsed time, add and subtract minutes to or from times. The following are some examples of what I want to do:
-Elapsed time (results can be positive or negative values)
31/2230 - 31/2220 = 10 (minutes)
31/2230 - 31/2240 = -10 (minutes)
01/0010- 30/2350 = 20 (minutes)
01/0605- 01/0555 = 10 (minutes)
01/1005- 01/1020 = -15 (minutes)

-I’m also looking to subtract minutes for the DATE/TIME as per the following examples:
01/0540 – 30 (minutes) =01/0510
01/0010 – 20 = 30/2350
10/0020 – 30 = 09/2350
01/0509 – 9 = 01/0500

It’s quite important to have all outputs in text or general formats as these outputs will be used somewhere else… thanks very much for any help 

[bebo021999]

-Elapsed time:
A1=31/2230
B1=31/2220
C1=IFERROR(MINUTE(A1-B1),-MINUTE(B1-A1))

Subtract minute:
A5=01/0540
B5=30
C5=A5-30*1/1440

[atchorizon]

thank you so much .. I really appreciate it... unfortunately, both formulas return #value! error... the date is not considered as well....... any other suggestions?????? .. once again thanks

[joeu2004]

The day/hhmm representation is ambiguous. Consequently, it is not possible unless we make some assumptions, which you neglect to articulate.

For example, you indicate that 01/0010 - 20 = 30/2350. Why not 31/2350? Or even 28/2350 or 29/2350?! Your other examples demonstrate that day 31 is allowed.

Similarly, you indicate that 01/0010 - 30/2350 = 20 (minutes). Why not 1460 minutes? That presumes day 31 is in between.

PS.... You indicate that the form day/hhmm is "formatted as text". Is it truly text; that is, ISTEXT(A1) returns TRUE? Or is it numeric Excel date/time formatted as Custom dd/hhmm; that is ISNUMBER(A1) returns TRUE? And is 20 (number of minutes to subtract) entered as text or as numeric? That is, if B1 displays 20, does ISNUMBER(B1) return TRUE?

[atchorizon]

thank you so much for the response.... I appreciate your efforts... all what you spoke about is true. there is too many conditions to consider when working with this problem.. in the examples I gave, my intention was to show that the date is to be considered whether the month is 31,30,29,or 28 days.. for example, 01/0010-20(min)=31/2350 if the month is of 31 days and will return 30/2350 if the month is 30 days, etc..

all dd/hhmm information and mm information are istext=true....

I have tried to sort out all assumption you spoke about and I have ended up nesting 50 if conditions and still getting some illogic outputs... in order to calculate dd/hhmm-mm as a text I used the following formula (which is not fully correct and I cannot fix it as I have reached the maximum allowed number of characters in the formula which is around 8100 characters) knowing that L50=dd/hhmm & M50=mm:

=IF(OR($L50="-",$M50="-",$L50="",$M50=""),"-",IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))>$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))=$M50),LEFT($L50,5)&"0"&(RIGHT($L50,2)-$M50),IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))<$M50),LEFT($L50,3)&(ABS(MID($L50,4,2)-1))&((ABS(RIGHT($L50,2))+60)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))<$M50),LEFT($L50,4)&(ABS(MID($L50,5,1))-1)&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))<$M50),(LEFT($L50,3))&(ABS(MID($L50,4,1))-1)&((ABS(MID($L50,5,1))+10)-1)&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))<$M50),LEFT($L50,3)&(ABS(MID($L50,4,2)-1))&((ABS(RIGHT($L50,2))+60)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))<$M50),LEFT($L50,4)&(ABS(MID($L50,5,1))-1)&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0,ABS(RIGHT($L50,2))<$M50),(LEFT($L50,3))&(ABS(MID($L50,4,1))-1)&((ABS(MID($L50,5,1))+10)-1)&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))<$M50),LEFT($L50,3)&(ABS(MID($L50,4,2)-1))&((ABS(RIGHT($L50,2))+60)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))<$M50),LEFT($L50,4)&(ABS(MID($L50,5,1))-1)&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))<$M50),(LEFT($L50,3))&(ABS(MID($L50,4,1))-1)&((ABS(MID($L50,5,1))+10)-1)&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))<$M50),LEFT($L50,3)&(ABS(MID($L50,4,2)-1))&((ABS(RIGHT($L50,2))+60)-$M50),IF(AND(ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))>0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))<$M50),LEFT($L50,4)&(ABS(MID($L50,5,1))-1)&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(MID($L50,4,1))>0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))=0,ABS(RIGHT($L50,2))<$M50),(LEFT($L50,3))&(ABS(MID($L50,4,1))-1)&((ABS(MID($L50,5,1))+10)-1)&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(LEFT($L50,1))=0,ABS(MID($L50,2,1))>1,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))=0),"0"&ABS(MID($L50,2,1))-1&"/"&24-1&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(LEFT($L50,1))=1,ABS(MID($L50,2,1))=0,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))=0),"0"&ABS(MID($L50,1,2))-1&"/"&24-1&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(LEFT($L50,2))>=11,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))=0),ABS(LEFT($L50,2))-1&"/"&ABS(MID($L50,4,2))+24-1&ABS(RIGHT($L50,2))+60-$M50,IF(AND(ABS(LEFT($L50,1))=0,ABS(MID($L50,2,1))>1,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0),"0"&ABS(MID($L50,2,1))-1&"/"&24-1&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(LEFT($L50,1))=1,ABS(MID($L50,2,1))=0,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0),"0"&ABS(MID($L50,1,2))-1&"/"&24-1&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(LEFT($L50,2))>=11,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))>0),ABS(LEFT($L50,2))-1&"/"&ABS(MID($L50,4,2))+24-1&ABS(RIGHT($L50,2))+60-$M50,IF(AND(ABS(LEFT($L50,1))=0,ABS(MID($L50,2,1))>1,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0),"0"&ABS(MID($L50,2,1))-1&"/"&24-1&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(LEFT($L50,1))=1,ABS(MID($L50,2,1))=0,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0),"0"&ABS(MID($L50,1,2))-1&"/"&24-1&(ABS(RIGHT($L50,2))+60)-$M50,IF(AND(ABS(LEFT($L50,2))>=11,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))=0,ABS(MID($L50,7,1))>0),ABS(LEFT($L50,2))-1&"/"&ABS(MID($L50,4,2))+24-1&ABS(RIGHT($L50,2))+60-$M50,IF(AND(ABS(LEFT($L50,1))=0,ABS(MID($L50,2,1))>1,ABS(MID($L50,4,1))=0,ABS(MID($L50,5,1))=0,ABS(MID($L50,6,1))>0,ABS(MID($L50,7,1))=0),"0"&ABS(MID($L50,2,1))-1&"/"&24-1&(ABS(RIGHT($L50,2))+60)-$M50)))))))))))))))))))))))))))))))))))))))))))))))


as you can see its quite complex and still missing some assumptions, for example, 12/1811-8=12/183 instead of 12/1803 !!!! so I was hoping to find simpler formula to calculate that especially that i'm looking at applying this on more than 100000 records per each workbook !!!!!
once again thanks for your efforts...

[joeu2004]

in the examples I gave, my intention was to show that the date is to be considered whether the month is 31,30,29,or 28 days.. for example, 01/0010-20(min)=31/2350 if the month is of 31 days and will return 30/2350 if the month is 30 days, etc..

Where is the month specified?!

all dd/hhmm information and mm information are istext=true

Surprise about "mm" (minutes)!

Aside.... I believe you use "mm" to represent number of minutes here. But Excel is ambiguous: "mm" is indeed minute of the hour when preceded by h (e.g. hhmm), or total minutes when enclosed in square brackets (i.e. [mm]). But it is month of the year by itself. So it behooves you to be more specific in your descriptions.

I have tried to sort out all assumption you spoke about and I have ended up nesting 50 if conditions

I suspect it does not have to be so complex, once you provide all the necessary information.

At this point, it seems that the only missing information is: where is the month of the year specified; and is it associated with the left-hand or right-hand "date/time" (day/hhmm)?

It would be prudent to attach an example Excel file. Click Go Advanced, then the Attach icon.

Also, you described two forms: day/hhmm - day/hhmm; and day/hhmm - min. Can we assume that you know which form you have; so you want 2 formulas? Or do you want a single formula that determines the form based on context (messier)?

[atchorizon]

the month is not specified in the dd/hhmm format .. the data gets extracted automativally from the DB per month..... as you can see in the attached files, I was tying to sort out all possible assumptions... I can make a plan where the formula can test the month first before determining number of days involved !!!!! I don't know

[joeu2004]

I have attached 2 files

Does either one contain the actual data as you see it? That is, is either one a subset of the actual Excel file?

I'm rushing out the door, but I logged on again to say....

When I said "attach an example Excel file", I meant an actual Excel file, perhaps reduced and redacted of private information, not an Excel file that you concoct to "demonstrate" the problem as you preceive (perhaps incorrectly).

I am still surprised that "minutes" alone is text. I wonder if it might have surrounding spaces or non-breaking spaces (HTML &nb).

So we need to see the actual data in its "original" form (i.e. original Excel); not something that you re-enter manually.

PS: I cannot make heads or tails of the larger Excel file that you attached. I suspect you concocted it to demonstrate the many combinations of something (je ne sais quoi) that you think might arise. If that's the case, you might be over-thinking the problem. Let's stick with the "original" data and only that for now.

the month is not specified in the dd/hhmm format .. the data gets extracted automativally from the DB per month

So are you suggesting that we use MONTH(TODAY()) to determine "the" month?

That would be a poor choice, since TODAY() changes every time you re-open the file. It might be Jan 31 one day, but Feb 1 the next day.

I don't know what control you have over the form of the data, but it would be better either to have the month for each day/hhmm, optionally in a parallel cell; or at least "the" month that the data was extracted in a single cell.

[atchorizon]

I have attached 2 files to show you what I was trying to do with a typical system extract ofmore than 30000 records to be used for testing purposes...

[atchorizon]

there should be two formulas, one to calculate dd/hhmm-mm(minutes)=dd/hhmm and the other one to calculate elapsed time dd/hhmm-dd/hhmm = mm (minutes) ... the attache file shows my try.. it seems to be working fine but it still very complex....