Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Time Calculation
- Thread starter LakotaMan
- Start date
Total time worked 14 hr 25 minutes 0 seconds
base time 7 hr 30 minutes 0 seconds
a1 @time(14,25,0)
b1 @time(7,30,0)
c1 =a1-b1 (and format to an hours/minutes/seconds time format)
I just looked at @functions, that is not very polished but maybe it will get you started. (ver 9.5 should be same as yours)
base time 7 hr 30 minutes 0 seconds
a1 @time(14,25,0)
b1 @time(7,30,0)
c1 =a1-b1 (and format to an hours/minutes/seconds time format)
I just looked at @functions, that is not very polished but maybe it will get you started. (ver 9.5 should be same as yours)
- Thread starter
- #3
This works fine, but. . .
The total hours are a result of a calculation, I have been trying to do the Time function on that calc as you've demonstrated, but having no luck. Any suggestions?
I'm very rusty with Lotus because I'm normally an Excel user, so I sometimes have trouble nesting functions and am not sure if this Time function can even be perfomed on another one.
Thanks again in advance!
The total hours are a result of a calculation, I have been trying to do the Time function on that calc as you've demonstrated, but having no luck. Any suggestions?
I'm very rusty with Lotus because I'm normally an Excel user, so I sometimes have trouble nesting functions and am not sure if this Time function can even be perfomed on another one.
Thanks again in advance!
I've not used macros or @functions extensively either; however when I saw your post, it sounded like something an @function would do. I did a little hunting and found the @time and thought well, convert total time worked to a number, convert the base time to a number, get the difference. The difference is a time number, display it by formatting in time format. In experimenting, I did find that I had to enter numbers for hours, minutes, AND seconds (even though I might not have any seconds) to make the @function work.
If I open a new workbook, and type @time(14,25,0) and press enter, the cell shows 0.6006944 . What happens on your system?
This is what's in the lotus help files for this at function:
@TIME(hour;minutes;seconds) calculates the time number for the specified hour, minutes, and seconds.
Arguments
hour is an integer from 0 (midnight) through 23 (11:00 PM).
minutes is an integer from 0 through 59.
seconds is an integer from 0 through 59.
Notes
Use a time format to make the time number appear as the time it represents.
Examples
The formula (@TIME(13;0;0)-@TIME(9;15;0))*95*24 calculates the amount due to a consultant on a given day by subtracting the start time from the stop time and multiplying the result by an hourly rate of $95.00.
Similar @functions
@TIMEVALUE converts labels to time numbers.
If I open a new workbook, and type @time(14,25,0) and press enter, the cell shows 0.6006944 . What happens on your system?
This is what's in the lotus help files for this at function:
@TIME(hour;minutes;seconds) calculates the time number for the specified hour, minutes, and seconds.
Arguments
hour is an integer from 0 (midnight) through 23 (11:00 PM).
minutes is an integer from 0 through 59.
seconds is an integer from 0 through 59.
Notes
Use a time format to make the time number appear as the time it represents.
Examples
The formula (@TIME(13;0;0)-@TIME(9;15;0))*95*24 calculates the amount due to a consultant on a given day by subtracting the start time from the stop time and multiplying the result by an hourly rate of $95.00.
Similar @functions
@TIMEVALUE converts labels to time numbers.
I didn't do too good a job reading your 2nd question did I?
Does the calculation give you a result in hours/mins etc or does it give you hours with a decimal fraction?
If it gives you hours/mins/seconds there should be a way to use text or numeric extraction from the cell to get figures into the formula. For example if cell b3 contained 14:30:00 and it was a text field @left(b3,2) should pull 14. So you could try @time(@left(b3,2),something for minutes,something for seconds). Not absolutely sure about this but that's the general approach of what I'd try.
If the hours are in decimal points I'm not sure what to do.
If that's the case, post back and maybe we'll get another reader to help us out.
Two other general comments, 1: once you get a general logic flow going you can probably make some range names for input areas and stick them in the formula so you don't have to tinker with the formula everytime. 2: If you happen to know the general approach if you were doing it in Excel instead of Lotus, you can then do a search on Lotus @functions. At least for basic things, I find they have similar functions and if I have to do something in Excel, I can think about Lotus and then hunt for similar functions in Excel. The reverse ought to work too.
Does the calculation give you a result in hours/mins etc or does it give you hours with a decimal fraction?
If it gives you hours/mins/seconds there should be a way to use text or numeric extraction from the cell to get figures into the formula. For example if cell b3 contained 14:30:00 and it was a text field @left(b3,2) should pull 14. So you could try @time(@left(b3,2),something for minutes,something for seconds). Not absolutely sure about this but that's the general approach of what I'd try.
If the hours are in decimal points I'm not sure what to do.
If that's the case, post back and maybe we'll get another reader to help us out.
Two other general comments, 1: once you get a general logic flow going you can probably make some range names for input areas and stick them in the formula so you don't have to tinker with the formula everytime. 2: If you happen to know the general approach if you were doing it in Excel instead of Lotus, you can then do a search on Lotus @functions. At least for basic things, I find they have similar functions and if I have to do something in Excel, I can think about Lotus and then hunt for similar functions in Excel. The reverse ought to work too.
- Thread starter
- #6
I have the cells formatted for time (i.e. 3:59 AM), and am getting hours, mins, etc. so I am assuming the calculation is numerical. I will see what I can do about extracting this. . .
I'll let you know if I can get it working, but it might be a day or two.
Thanks for all your help!
I'll let you know if I can get it working, but it might be a day or two.
Thanks for all your help!
Maybe I was making things too complicated.
Does that mean that the contents of the calculated cell is already computed as a time number JUST DISPLAYED as time?
If so could you just do [calculated cell] less [time value of 7hr 30 min (@time(7,30,0)] = time difference?
Format time difference to display in hr/min.
If you then have to have hrs, mins etc for further calcs
I found @hour, @minute, @second which would extract those numbers.
I'm in trouble on a project-probably wont be able to check back again until tomorrow.
Does that mean that the contents of the calculated cell is already computed as a time number JUST DISPLAYED as time?
If so could you just do [calculated cell] less [time value of 7hr 30 min (@time(7,30,0)] = time difference?
Format time difference to display in hr/min.
If you then have to have hrs, mins etc for further calcs
I found @hour, @minute, @second which would extract those numbers.
I'm in trouble on a project-probably wont be able to check back again until tomorrow.
- Thread starter
- #8
Yes, that's exactly what's going on. . .
I tried that:
([calculated cell] less [time value of 7hr 30 min (@time(7,30,0)] = time difference)
. . . it didn't work, so that's when I figured I'd need a fancy @function to make it happen!
I was hoping there would be an equivalent of the datediff function for hours, but haven't seen that in help at all!
Whew. . . what a mess!
I'll keep plugging at it!
I tried that:
([calculated cell] less [time value of 7hr 30 min (@time(7,30,0)] = time difference)
. . . it didn't work, so that's when I figured I'd need a fancy @function to make it happen!
I was hoping there would be an equivalent of the datediff function for hours, but haven't seen that in help at all!
Whew. . . what a mess!
I'll keep plugging at it!
I'll have to read about datediff in excel.
But now I'm wondering about the calculated field.
Playing with @time, it looks like the calculated field would have to be numeric and have a value between .3125 and .9999884 for that approach we discussed to work.
Does it meet those criteria?
But now I'm wondering about the calculated field.
Playing with @time, it looks like the calculated field would have to be numeric and have a value between .3125 and .9999884 for that approach we discussed to work.
Does it meet those criteria?
- Thread starter
- #12
I agree, I think we're supposed to do it with the date functions.
the only other suggestion I can offer is
@time(@hour(calculated-cell),@minute(calculated-cell),@second(calculated-cell))-@time(7,30,0)
but I think that should be the same as
calculated-cell - @time(7,30,0)
the only other suggestion I can offer is
@time(@hour(calculated-cell),@minute(calculated-cell),@second(calculated-cell))-@time(7,30,0)
but I think that should be the same as
calculated-cell - @time(7,30,0)
:~/ :~/ I guess I'm one of those whining at your desk for leniency. I said date but I used time. I meant time do I still get my A?
I was thinking about the posts some more. I don't know the symptoms of how what you were doing didn't work. When I was experimenting with these things, if I didn't get ( ) right for nesting, the cell entry just sat there and looked at me with a blinking cursor (unless it was a missing final ), in which case it added it. When I got "err" - i'm not sure, but i think it was from using numeric formulas/functions on a text field or vice-versa. Perhaps those behaviors can be additional clues for you when you get back to it.
Good luck - and again I apologize for the extra posts from carelessness.
I was thinking about the posts some more. I don't know the symptoms of how what you were doing didn't work. When I was experimenting with these things, if I didn't get ( ) right for nesting, the cell entry just sat there and looked at me with a blinking cursor (unless it was a missing final ), in which case it added it. When I got "err" - i'm not sure, but i think it was from using numeric formulas/functions on a text field or vice-versa. Perhaps those behaviors can be additional clues for you when you get back to it.
Good luck - and again I apologize for the extra posts from carelessness.
I just found something interesting that might be helpful to you in trouble shooting the problem.
There is an information function called @cell.
Among others it has the values datatype and formulatype.
So @cell("formulatype",ref of calculated cell) or
@cell("datatype",ref of calculated cell)
might help to get a clearer picture of whether the calculated cell contains numeric or text, which in turn should help in clarifying whether to use @time or @timevalue on it to get the number to run against @time for 7hr 30min 0sec .
This next comment is not relevant to the problem at hand but relates to something mentioned in passing above which you also made me curious about.
I was looking through ref materials for Excel5 and Lotus97.(I'm too cheap to spend big bucks on newer books!)
The only place I can find a combined date.time serial number is in the NOW function. So in theory, I guess you could save a bunch of sequential NOW readings, compute differences and compute elapsed time based on the time portion of serial number differences. The rest of the functions seem to be either date ( >1 ) serials or time ( <1 ) serials -- and that seems to be similar in Excel and Lotus.
There is an information function called @cell.
Among others it has the values datatype and formulatype.
So @cell("formulatype",ref of calculated cell) or
@cell("datatype",ref of calculated cell)
might help to get a clearer picture of whether the calculated cell contains numeric or text, which in turn should help in clarifying whether to use @time or @timevalue on it to get the number to run against @time for 7hr 30min 0sec .
This next comment is not relevant to the problem at hand but relates to something mentioned in passing above which you also made me curious about.
I was looking through ref materials for Excel5 and Lotus97.(I'm too cheap to spend big bucks on newer books!)
The only place I can find a combined date.time serial number is in the NOW function. So in theory, I guess you could save a bunch of sequential NOW readings, compute differences and compute elapsed time based on the time portion of serial number differences. The rest of the functions seem to be either date ( >1 ) serials or time ( <1 ) serials -- and that seems to be similar in Excel and Lotus.
- Status
Similar threads
- Locked
- Question