# time calculations

1. ## time calculations

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   Register To Reply

2. ## Re: time calculations

-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  Register To Reply

3. ## Re: time calculations

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  Register To Reply

4. ## Re: time calculations

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?  Register To Reply

5. ## Re: time calculations

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...  Register To Reply

6. ## Re: time calculations Originally Posted by atchorizon 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?! Originally Posted by atchorizon all dd/hhmm information and mm information are istext=true

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. Originally Posted by atchorizon 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)?  Register To Reply

7. ## Re: time calculations

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...  Register To Reply

8. ## Re: time calculations

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   Register To Reply

9. ## Re: time calculations Originally Posted by atchorizon 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. Originally Posted by atchorizon 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.  Register To Reply

10. ## Re: time calculations

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....  Register To Reply

11. ## Re: time calculations

it sounds a great idea 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. I will attach an example of my system extract with blank fields that require calculations  Register To Reply

12. ## Re: time calculations

the attached file is a typical extract from the system with highlighted columns needs to be calculated. I have included only 8 days of data as the file is too big to be uploaded... thanks so much for your help and advise...  Register To Reply

13. ## Re: time calculations

by the way... I have tested the mm (minuets) its a numeric (istext=false)....... but the rest are text  Register To Reply

14. ## Re: time calculations Originally Posted by atchorizon the attached file is a typical extract from the system with highlighted columns needs to be calculated.
See the formulas in columns K and P in the attached file. I had to truncate the file to 2000 rows in order to fit the attachment limit. (Caveat: The original attachment had freeze-top set. I attempted to replace it. But if you find the top row (titles) is "frozen", you can unfreeze by clicking View, Freeze Panes, Unfreeze Panes.)

I leave it to you to copy and modify the formulas into columns L:N and O.

Note the formula in O2 and P2 is array-entered by selecting a single cell and pressing ctrl+shift+Enter instead of just Enter.

Since I did not find month numbers for each date/time (dd/hhmm), I implemented the following heuristic.

In K2 (essentially =I2-G2), if I2 is 01/xxxx and G2 28/xxxx or greater, I assume that I2 is an appropriate month before a month with 28, 29, 30 or 31 days.

In P2 (essentially =H2-Y2), if H2 is 01/xxxx, I try to intuit the current month based on the max month in G2:J2 and Q2:T2, just in case subtracting Y2 minutes would result in the last day of the previous month.

I'll try to explain the theory of operation of each formula. Ask questions, if you need clarification.

K2: ``Please Login or Register  to view this content.``
The text date in the form dd/hhmm is converted into numeric Excel date/time using DATE(year,month,day)+TEXT("hhmm","00\:00").

The year is 2015 or 2016, an arbitrary normal and leap year. We choose 2016 if I2 is day 01 and G2 is day 28 to 31.

The month is July, an arbitrary 31-day month, unless I2 is 01 and G2 is day 28 to 31. In that case, G2 is month 2, 6 or 7, arbitrary months with the correct number of days; and I2 is the next month.

The two numeric Excel date/times are subtracted, then multiplied by 1440 to convert to a number of minutes. The calculation is rounded to an integer in order to avoid arithmetic anomalies that occur with non-integer values (time of day) that are represented internally using 64-bit binary floating-point.

P2: ``Please Login or Register  to view this content.``
Again, the text date in the form dd/hhmm in H2 is converted into numeric Excel date/time using DATE(year,month,day)+TEXT("hhmm","00\:00").

Also, the number of minutes in Y2 is converted into numeric Excel time (date=0).

For H2, the year is 2016 if the max day in G2:I2 and Q2:T2 is 29; otherwise, the year is 2015.

The month is 8, an arbitrary 31-day month, unless the max day in G2:I2 and Q2:T2 is 28 to 30. In that case, the month is 3 or 7, so the day resulting from the calculation might be the last day of the previous month (2 or 6).

I hope these assumptions meet your needs.  Register To Reply

15. ## Re: time calculations

YOU ARE A STAR ...... its working like a machine....... your idea is brilliant..... you were right, I was over thinking the problem ,,,, I don't know how to thank you, I really appreciate your help   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 