+ Reply to Thread
Results 1 to 15 of 15

time calculations

  1. #1
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    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 its 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)

    -Im 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

    Its 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 

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,080

    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

  3. #3
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    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

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,585

    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?
    Last edited by joeu2004; 12-25-2015 at 04:32 PM. Reason: PS

  5. #5
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    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...

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,585

    Re: time calculations

    Quote Originally Posted by atchorizon View Post
    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?!

    Quote Originally Posted by atchorizon View Post
    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.

    Quote Originally Posted by atchorizon View Post
    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)?
    Last edited by joeu2004; 12-25-2015 at 05:20 PM. Reason: modified last question

  7. #7
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    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...
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    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

  9. #9
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,585

    Re: time calculations

    Quote Originally Posted by atchorizon View Post
    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.

    Quote Originally Posted by atchorizon View Post
    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.

  10. #10
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    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....

  11. #11
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    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

  12. #12
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    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...
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    Re: time calculations

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

  14. #14
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,585

    Re: time calculations

    Quote Originally Posted by atchorizon View Post
    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.
    Attached Files Attached Files
    Last edited by joeu2004; 12-26-2015 at 01:34 PM. Reason: errata for K2: I2 is next month; simplify copy-formula instructions

  15. #15
    Registered User
    Join Date
    02-13-2015
    Location
    Johannesburg
    MS-Off Ver
    office 2013
    Posts
    11

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1