Times and a 24hr clock

[RedForeman ©®]

expression fields
Incisional Time: [SkinCutStartTime]-[SkinCutStopTime]

What's the expression used to calculate number of minutes between the
beginning and end of times? This will overlap at midnight, so I have to
consider that too. I've had success with a couple, but minutes are ok,
hours are wrong, etc...
10:00am to 2:00pm should show 240minutes, I get 40minutes, I'm losing the
hours, so I'm assuming it's because of the AM/PM change, or a 24hr clock...

any help or suggestions are appreciated....


--
RedForeman ©® future fabricator and creator of a ratbike streetfighter!!!
==========================
2003 TRX450ES
1992 TRX-350 XX (For Sale)
'98 Tacoma Ext Cab 4X4 Lifted....
==========================
ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸,ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸,ø¤°`°¤ø


is that better??

 

[Gary Walter]

expression fields
Incisional Time: [SkinCutStartTime]-[SkinCutStopTime]

What's the expression used to calculate number of minutes between the
beginning and end of times? This will overlap at midnight, so I have to
consider that too. I've had success with a couple, but minutes are ok,
hours are wrong, etc...
10:00am to 2:00pm should show 240minutes, I get 40minutes, I'm losing the
hours, so I'm assuming it's because of the AM/PM change, or a 24hr clock...

Hi Red,

Another method would be to use DateDiff function:

StartTime=#4/13/04 10:00 AM#
StopTime=#4/13/04 2:00 PM#
?DateDiff("n",StartTime, StopTime)
240

or to put in hh:mm format:

?Format(DateDiff("n",StartTime,StopTime)\60,"00") & ":" &
Format(DateDiff("n",StartTime,StopTime) Mod 60,"00")
04:00

 

[George Nicholson]

There are 1440 minutes in a day, therefore:
([SkinCutStartTime]-[SkinCutStopTime])*1440
should give you the number of minutes between the two events.

Note that dates and times are part of a single number. Date info is in the
integer portion, Time info is in the decimal portion.
1 represents both 1 day and Jan 1, 1900, depending on usage.
0.041667 (i.e., 1/24th) represents both 1 hour and 1:00 AM.

 

[RedForeman ©®]

There are 1440 minutes in a day, therefore:
([SkinCutStartTime]-[SkinCutStopTime])*1440
should give you the number of minutes between the two events.

Note that dates and times are part of a single number. Date info is in the
integer portion, Time info is in the decimal portion.
1 represents both 1 day and Jan 1, 1900, depending on usage.
0.041667 (i.e., 1/24th) represents both 1 hour and 1:00 AM.

George, excellent post, one quick question... Using your query, I get
several negative numbers, I can only assume this is because of the
overlapping times after midnight, for example
Start Stop Result
11:43 PM 1:42 AM -1321

SO for those negative, or overlapping times, they need to be multiplied by
1440 again, to become the correct answer of 119 in this case... where do I
fix this??

Another quickie, on several records, there are running zeros, like
95.00000000001, and 89.999999999, etc.... how can I just cap it off at the
decimal, or round?

--
RedForeman ©® future fabricator and creator of a ratbike streetfighter!!!
==========================
2003 TRX450ES
1992 TRX-350 XX (For Sale)
'98 Tacoma Ext Cab 4X4 Lifted....
==========================
ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸,ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸,ø¤°`°¤ø

 

[RedForeman ©®]

expression fields
Incisional Time: [SkinCutStartTime]-[SkinCutStopTime]

What's the expression used to calculate number of minutes between the
beginning and end of times? This will overlap at midnight, so I have to
consider that too. I've had success with a couple, but minutes are ok,
hours are wrong, etc...
10:00am to 2:00pm should show 240minutes, I get 40minutes, I'm losing the
hours, so I'm assuming it's because of the AM/PM change, or a 24hr clock...

Hi Red,

Another method would be to use DateDiff function:

StartTime=#4/13/04 10:00 AM#
StopTime=#4/13/04 2:00 PM#
?DateDiff("n",StartTime, StopTime)
240

I used your example above, as I'm looking for minutes... but this is where I
am with this query....

Times2: Format(DateDiff("n",[SkinCutStopTime],[SkinCutStartTime]\60,"00"))

Problem is it returns ALL negative numbers, all over the place kinda
numbers...

Time99: Format(DateDiff("n",[SkinCutStopTime],[SkinCutStartTime])*1440,"00")
gives complete incorrect answers.. thus, I'm skeptical of this query... so
far, the next one has resulted good info, just some overlapping negative
numbers... due to the start in pm end in am

Thanks...

 

[Gary Walter]

note earlier date goes first:

DateDiff(interval, Earlierdate1, Laterdate2)

using "n" *gives* you number total minutes,
so if all you want is elapsed minutes

DateDiff("n",[SkinCutStartTime], [SkinCutStopTime])

expression fields
Incisional Time: [SkinCutStartTime]-[SkinCutStopTime]

What's the expression used to calculate number of minutes between the
beginning and end of times? This will overlap at midnight, so I have to
consider that too. I've had success with a couple, but minutes are ok,
hours are wrong, etc...
10:00am to 2:00pm should show 240minutes, I get 40minutes, I'm losing the
hours, so I'm assuming it's because of the AM/PM change, or a 24hr clock...

Hi Red,

Another method would be to use DateDiff function:

StartTime=#4/13/04 10:00 AM#
StopTime=#4/13/04 2:00 PM#
?DateDiff("n",StartTime, StopTime)
240

I used your example above, as I'm looking for minutes... but this is where I
am with this query....

Times2: Format(DateDiff("n",[SkinCutStopTime],[SkinCutStartTime]\60,"00"))

Problem is it returns ALL negative numbers, all over the place kinda
numbers...

Time99: Format(DateDiff("n",[SkinCutStopTime],[SkinCutStartTime])*1440,"00")
gives complete incorrect answers.. thus, I'm skeptical of this query... so
far, the next one has resulted good info, just some overlapping negative
numbers... due to the start in pm end in am

Thanks...

 

[RedForeman ©®]

note earlier date goes first:

DateDiff(interval, Earlierdate1, Laterdate2)

using "n" *gives* you number total minutes,
so if all you want is elapsed minutes

DateDiff("n",[SkinCutStartTime], [SkinCutStopTime])

Thanks, that works, but for the absolute last bugging question..... is the
overlapping midnight times, they're showing in negative numbers...

They are a result of this start @ 10:00pm, stop @ 1:00am resulting in -1260,
and if I add 1440 to it, it becomes the correct answer... 180...where do I
add that condition for the negative numbers???

I also have some numbers that are showing decimal places, and I don't want
to... know how to rid them?

Thanks you've been a great help...

 

[Rick Brandt]

note earlier date goes first:

DateDiff(interval, Earlierdate1, Laterdate2)

using "n" *gives* you number total minutes,
so if all you want is elapsed minutes

DateDiff("n",[SkinCutStartTime], [SkinCutStopTime])

Thanks, that works, but for the absolute last bugging question..... is the
overlapping midnight times, they're showing in negative numbers...

If you're going to cross midnight then you need to store the date as well
as the time.

 

[RedForeman ©®]

DateDiff("n",[SkinCutStartTime], [SkinCutStopTime])

If you're going to cross midnight then you need to store the date as
well as the time.

Ok, I've got a Surgery_Date field, where do I throw that in??

Time1: (([SkinCutStopTime]-[SkinCutStartTime])*1440)
and
Times2: DateDiff("n",[SkinCutStartTime],[SkinCutStopTime])

Both give correct number, EXCEPT where crossing midnight... So, if I put the
Surgery_Date in, I'm not sure where to put it in...

--
RedForeman ©® future fabricator and creator of a ratbike
streetfighter!!! ==========================
2003 TRX450ES
1992 TRX-350 XX (For Sale)
'98 Tacoma Ext Cab 4X4 Lifted....
==========================
ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸,ø¤°`°¤ø,¸¸¸,ø¤°`°¤ø,¸¸,ø¤°`°¤ø


is that better??

 

[Douglas J. Steele]

DateDiff("n",[SkinCutStartTime], [SkinCutStopTime])

Thanks, that works, but for the absolute last bugging question.....
is the overlapping midnight times, they're showing in negative
numbers...

If you're going to cross midnight then you need to store the date as
well as the time.

Ok, I've got a Surgery_Date field, where do I throw that in??

Time1: (([SkinCutStopTime]-[SkinCutStartTime])*1440)
and
Times2: DateDiff("n",[SkinCutStartTime],[SkinCutStopTime])

Both give correct number, EXCEPT where crossing midnight... So, if I put the
Surgery_Date in, I'm not sure where to put it in...

You need the date when the surgery started and the date when it ended.
That's because the Date/Time data type is intended for a point-in-time (i.e.
a timestamp) value. It stores its values as 8 byte floating point numbers,
where the integer part represents the date as the number of days relative to
30 Dec, 1899, and the decimal part represents the time as a fraction of a
day. When you only store a time, it assumes it's that time on 30 Dec, 1899,
which is what's causing you your problem.

Something that works is shown at
http://www.mvps.org/access/datetime/date0008.htm but in my opinion it's
really just a band-aid to allow people to perpetuate poor practices.

 

[John Spencer (MVP)]

This gets complex since you are only storing the times and not the dates. As
long as you don't have a surgery go over 24 hours, you can do something like

IIF(SkinCutStartTime<=SkinCutEndTime,
DateDiff("n",SkinCutStartTime,SkinCutEndTime),
DateDiff("n",SkinCutStartTime,1+SkinCutEndTime))

OR possibly

DateDiff("n", SkinCutStartTime,1 + (SkinCutStartTime<=SkinCutEndTime) + SkinCutEndTime)

The two different statements above are NOT TESTED. I believe they should work,
but test them on a few of your record sets.

There are 1440 minutes in a day, therefore:
([SkinCutStartTime]-[SkinCutStopTime])*1440
should give you the number of minutes between the two events.

Note that dates and times are part of a single number. Date info is in the
integer portion, Time info is in the decimal portion.
1 represents both 1 day and Jan 1, 1900, depending on usage.
0.041667 (i.e., 1/24th) represents both 1 hour and 1:00 AM.

George, excellent post, one quick question... Using your query, I get
several negative numbers, I can only assume this is because of the
overlapping times after midnight, for example
Start Stop Result
11:43 PM 1:42 AM -1321

SO for those negative, or overlapping times, they need to be multiplied by
1440 again, to become the correct answer of 119 in this case... where do I
fix this??

Another quickie, on several records, there are running zeros, like
95.00000000001, and 89.999999999, etc.... how can I just cap it off at the
decimal, or round?

--
RedForeman ©® future fabricator and creator of a ratbike streetfighter!!!
==========================
2003 TRX450ES
1992 TRX-350 XX (For Sale)
'98 Tacoma Ext Cab 4X4 Lifted....
==========================
ø€°`°€ø,¸¸¸,ø€°`°€ø,¸¸¸,ø€°`°€ø,¸¸,ø€°`°€ø,¸¸¸,ø€°`°€ø,¸¸,ø€°`°€ø

 

[RedForeman ©®]

You need the date when the surgery started and the date when it ended.

That's because the Date/Time data type is intended for a
point-in-time (i.e. a timestamp) value. It stores its values as 8
byte floating point numbers, where the integer part represents the
date as the number of days relative to 30 Dec, 1899, and the decimal
part represents the time as a fraction of a day. When you only store
a time, it assumes it's that time on 30 Dec, 1899, which is what's
causing you your problem.

The surgery will never be that long, but some will start on April 13, 2004 @
23:30 and end on April 14, 2004 @ 1:30, so how would that fare?

Something that works is shown at
http://www.mvps.org/access/datetime/date0008.htm but in my opinion
it's really just a band-aid to allow people to perpetuate poor
practices.

thanks for that, but I'm looking more and more and just can't see another
way, except to insert Surgery_Date and where to put it, is what's
perplexing....