Solved

Calculating Time

Posted on 2015-02-23
30
85 Views
Last Modified: 2016-02-10
I'm using the following code to calculate elapsed time.  It works fine until I go over 24 hours.  The field is formatted as short time which also works fine until the value exceeds 24 hours.  I don't want days, Hours, and Minutes.  I just want hours and minutes.  I believe the problem is the data type or formatting of the field, not the formula.  Please help.

=(((IIf([PFT]>[PMRT],[PFT]-[PMRT],([PFT]+1)-[PMRT])*[PressOps])*1440)\60) & Format(((IIf([PFT]>[PMRT],[PFT]-[PMRT],([PFT]+1)-[PMRT])*[PressOps])*1440) Mod 60,"\:00")

Marshall
0
Comment
Question by:Mwvarner
  • 14
  • 10
  • 3
  • +3
30 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40625874
Hi

To be able to do a SUM in houres, you need to use format below in the cell with the formula :
[hh]:mm

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40625887
The time format is intended to show a point in time.  That is different from elapsed time.  To show elapsed time over 24 hours.  Calculate the difference as minutes and then convert to hours and minutes by dividing by 60 and concatenate the results with the ":" separator.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40625892
can you give me an example of what you have now, and what you are expecting to doo?

Is it for example

12:55:00
13:55:00
14:55:00
15:55:00
16:55:00


Would equal to: 74:35
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40625900
You can use this function:
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String
  
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute
  
  FormatHourMinute = strHourMinute
  
End Function

Open in new window

/gustav
0
 
LVL 84
ID: 40625908
The field is formatted as short time which also works fine until the value exceeds 24 hours.
The Formatting of a data field is of no consequence when you run calculations on it, unless you first FORMAT the data, and then try to run calcs on that formatted object.

If you are actually storing ONLY the HH;MM, then you're out of luck.

If you are storing the FULL DateTime (like 01-01-2014T12:22:34) then you should be using DateDiff to get the variance:

'/ number of MInutes between the two
DateDiff("n", Date1, Date2)

See more information on DateDiff here:
https://support.office.com/en-us/article/DateDiff-Function-e6dd7ee6-3d01-4531-905c-e24fc238f85f?ui=en-US&rs=en-US&ad=US
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40625914
Please have a look at the attachment.
Sum-on-time.xlsx
0
 

Author Comment

by:Mwvarner
ID: 40625989
I've been doing this in a Macro, not VBA but I can change if I can figure out the proper code.  This formula I'm using now compares the start time which is "PMRT" to the End Time which is "PFT" to determine if the time period crosses midnight.  Then depending on that it subtracts the start time from end time in minutes, divides by 60 for hours and then shows the remaining minutes them multiplies that number by the number of people working on the job, "PressOps" and stores the results in a field which is a Short Text field.  I had the field formatted as short time which I realize now will not work for values over 24 hours.  What I need to know is how to format the field to show only hours and minutes when over 24 hours.  



=(((IIf([PFT]>[PMRT],[PFT]-[PMRT],([PFT]+1)-[PMRT])*[PressOps])*1440)\60) & Format(((IIf([PFT]>[PMRT],[PFT]-[PMRT],([PFT]+1)-[PMRT])*[PressOps])*1440) Mod 60,"\:00")
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40626006
Use my function (copy paste into an empty module):

=FormatHourMinute(([PFT]-[PMRT])*[PressOps])

The output is a string so apply no format.

/gustav
0
 

Author Comment

by:Mwvarner
ID: 40626016
This function will return an incorrect value if the start and end times cross midnight.

Marshall
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40626041
So you have time only. Then:

=FormatHourMinute(([PFT]-[PMRT]+Abs([PFT]<[PMRT]))*[PressOps])

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40626066
I don't want days, Hours, and Minutes.  I just want hours and minutes.
Unlike Excel, Access has no built-in format or formula to show a time period of more than 24 hours in length as just hours and minutes.
You must create, or borrow, a VBA function to do the job for you.
And the format of the control you display your result in MUST NOT be a datetime format, or it will just undo your hard work.

You may already be fine
The field is formatted as short time
Change the format to General and see what happens.
0
 

Author Comment

by:Mwvarner
ID: 40626096
I've removed all formatting from the field on the form and the table.  I've  pasted your function into an module and I've edited the macro to the =FormatHourMinute(([PFT]-[PMRT]+Abs([PFT]<[PMRT]))*[PressOps])  After this the value still shows up as a time,   4:00 hours * 5 PressOps shows up as 8:00:00 PM which is 20:00.  I don't know what is formatting the filed to show PM now.
0
 

Author Comment

by:Mwvarner
ID: 40626097
It also is not working on values that would exceed 24 hours or 24:00.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40626128
The function works. Here's a test:
Public Sub test()

    Dim PMRT        As Date
    Dim PFT         As Date
    Dim PressOps    As Integer
    
    Dim D           As Integer
    
    ' Start
    PMRT = #9:00:00 AM#
    ' End
    PFT = #8:00:00 AM#
    
    PressOps = 1
    Debug.Print FormatHourMinute((PFT - PMRT + Abs(PFT < PMRT)) * PressOps)
    PressOps = 5
    Debug.Print FormatHourMinute((PFT - PMRT + Abs(PFT < PMRT)) * PressOps)
    PressOps = 10
    Debug.Print FormatHourMinute((PFT - PMRT + Abs(PFT < PMRT)) * PressOps)
    
End Sub

Open in new window

Output is:

23:00
115:00
230:00

Perhaps your values are not date/time?
Try:

=FormatHourMinute((CDate([PFT])-CDate([PMRT])+Abs(CDate([PFT])<CDate([PMRT])))*[PressOps])

/gustav
0
 

Author Comment

by:Mwvarner
ID: 40626158
I get the same results, The PMRT and PFT fields are both set as short text and format is Medium time.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Mwvarner
ID: 40626170
I pasted the line in the macro and I get an error that says the database can't parse the expression.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40626174
The format doesn't matter; it is for display only.
What matters are the values and the data type.

If data type is Date, the function and calculation works as is.
If data type is Text, the values must be converted using CDate before passed to the function and calculation can be performed.

The output is text because a time value of more than 24 hours cannot be displayed using a time format.
Thus, the format of the output should be none or Standard.

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40626179
Could you replicate here please, what you pasted and where?

/gustav
0
 

Author Comment

by:Mwvarner
ID: 40626190
See the attached PDF file.
Error.pdf
0
 

Author Comment

by:Mwvarner
ID: 40626238
I did paste your test function into a module and it does work properly, even for values over 24 hours.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40626347
Oops, I don't do macros, so I can't tell.

Couldn't you just open the Properties panel and select Events, OnClick and Event Procedure and open the code Windows.
Then insert this code line:

Me!ManHrs.Value = FormatHourMinute((CDate([PFT])-CDate([PMRT])+Abs(CDate([PFT])<CDate([PMRT])))*[PressOps])

/gustav
0
 

Author Comment

by:Mwvarner
ID: 40626378
See attached pdf file.  I'm still getting an error message.  Should I drop the macro and try to do it all in vba?
0
 

Author Comment

by:Mwvarner
ID: 40626379
File attached to this one.
Time-Calculation.pdf
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40626409
>  Should I drop the macro and try to do it all in vba?

I would recommend that.

/gustav
0
 

Author Comment

by:Mwvarner
ID: 40626420
OK,  I think I have the basics worked out for the VBA.  Your test function does give the correct results. I've set the variables to read from the form already so it picks up the values from PMRT, PFT and PressOps and calculates the correct value.  However I can't seem to get the code to populate the manhrs field on the form to work.  I don't get any errors, just nothing seems to happen.
0
 

Author Comment

by:Mwvarner
ID: 40626477
Also can we get it all rolled into one function?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40626563
What does your OnClick sub function look like?

/gustav
0
 

Author Comment

by:Mwvarner
ID: 40626602
I don't have anything on the onClick sub function.  I have a after update function on the PressOps field.  

Private Sub PressOps_Change()
  Call MyFunctions.Test
End Sub

The test sub is what you sent me with a couple of modifications to pull the data from the form.  Here it is.  It works when I run it manually from the VBA window but I can't get the call to work.  The test Sub also used your FormatHourMinute function.

Public Sub Test()

    Dim PMRT        As Date
    Dim PFT         As Date
    Dim PressOps    As Integer
    Dim ET As String
   
   
    ' Start
    PMRT = Forms!FrmEditPressRpt!PMRT.Value
    ' End
    PFT = Forms!FrmEditPressRpt!PFT.Value
   
    PressOps = Forms!FrmEditPressRpt!PressOps.Value
   Debug.Print FormatHourMinute((PFT - PMRT + Abs(PFT < PMRT)) * PressOps)
   ET = FormatHourMinute((PFT - PMRT + Abs(PFT < PMRT)) * PressOps)
   Debug.Print ET
   Forms!FrmEditPressRpt!ManHrs.Value = ET
    DoCmd.SetProperty "forms!frmEditPressRpt!ManHrs", acPropertyValue, ET
       
End Sub
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40626700
OK. You shouldn't use the Change event (remove this) but the AfterUpdate:

Private Sub PressOps_AfterUpdate()
    Me!ManHrs.Value = FormatHourMinute(([PFT] - [PMRT] + Abs([PFT] < [PMRT])) * [PressOps])
End Sub

assuming that this is in the form FrmEditPressRpt and [PFT] and [PMRT] are bound to date fields.

/gustav
0
 

Author Closing Comment

by:Mwvarner
ID: 40626732
Gustav I can't thank you enough.  This is so much easier than the way I was trying to do it and I learned so much about VBA today.  I have similar places in this DB where I'm working with date time fields and this will help so much.  I wish I could give you 10 times the points .  Thanks for sticking with me through this.

Marshall
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now