Time in Minutes

[Guest]

I have one [Date] and two time fields [start] and [finish]. The time cycle is
either 9am-9pm OR 9pm-9am, so in one case the date of [finish] changes and in
another it doesn't. I guess if I had two date fields [st_date] and
[end_date], it will be easier to perform calculations. Unfortunately end_date
is not given.

I am able to get the results in "HH:MM" (11:40) format through the following
statement.

Expr1: Format((DateDiff("n",Format([Date],"mm/dd/yyyy") & " " &
[Start],Format([Date]+1,"mm/dd/yyyy") & " " & [Finish])/1440),"Short Time")

Question:
1. How do I get time in minutes? (like 700 min.)
2. Am I using the right method or there is a better way to do this?

I also tried adding "hh:nn" into date format, it gives an error.

Any suggestion is highly appreciated. Thanks in advance.

Doug

 

[Allen Browne]

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[Guest]

Thanks Allen for your quick response. I checked the link but did not get any
specific help that I need. Excuse my inexperience in the subject. I am
trying to learn things. Can you tell me specific solution based upon the SQL
i have posted? Thanks again.


=============

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have one [Date] and two time fields [start] and [finish]. The time cycle
is
either 9am-9pm OR 9pm-9am, so in one case the date of [finish] changes and
in
another it doesn't. I guess if I had two date fields [st_date] and
[end_date], it will be easier to perform calculations. Unfortunately
end_date
is not given.

I am able to get the results in "HH:MM" (11:40) format through the
following
statement.

Expr1: Format((DateDiff("n",Format([Date],"mm/dd/yyyy") & " " &
[Start],Format([Date]+1,"mm/dd/yyyy") & " " & [Finish])/1440),"Short
Time")

Question:
1. How do I get time in minutes? (like 700 min.)
2. Am I using the right method or there is a better way to do this?

I also tried adding "hh:nn" into date format, it gives an error.

Any suggestion is highly appreciated. Thanks in advance.

 

[Allen Browne]

To get the number of minutes, just use DateDiff():
Expr1: DateDiff("n",[Date],[Finish])

If it still fails, it might be because DATE is a reserved word. I imagine
you would get away with it in this particular case, but it will give you
problems in other scenarios. You could add the table name, e.g.:
Expr1: DateDiff("n",[Table1].[Date],[Finish])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen for your quick response. I checked the link but did not get
any
specific help that I need. Excuse my inexperience in the subject. I am
trying to learn things. Can you tell me specific solution based upon the
SQL
i have posted? Thanks again.


=============

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

I have one [Date] and two time fields [start] and [finish]. The time
cycle
is
either 9am-9pm OR 9pm-9am, so in one case the date of [finish] changes
and
in
another it doesn't. I guess if I had two date fields [st_date] and
[end_date], it will be easier to perform calculations. Unfortunately
end_date
is not given.

I am able to get the results in "HH:MM" (11:40) format through the
following
statement.

Expr1: Format((DateDiff("n",Format([Date],"mm/dd/yyyy") & " " &
[Start],Format([Date]+1,"mm/dd/yyyy") & " " & [Finish])/1440),"Short
Time")

Question:
1. How do I get time in minutes? (like 700 min.)
2. Am I using the right method or there is a better way to do this?

I also tried adding "hh:nn" into date format, it gives an error.

Any suggestion is highly appreciated. Thanks in advance.

 

[Guest]

Tried this but gives values in negative. Is there any way that I can convert
"HH:MM" (like 11:30) into "Minutes" (like 690 Minutes)?

Thank you.
----------

To get the number of minutes, just use DateDiff():
Expr1: DateDiff("n",[Date],[Finish])

If it still fails, it might be because DATE is a reserved word. I imagine
you would get away with it in this particular case, but it will give you
problems in other scenarios. You could add the table name, e.g.:
Expr1: DateDiff("n",[Table1].[Date],[Finish])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen for your quick response. I checked the link but did not get
any
specific help that I need. Excuse my inexperience in the subject. I am
trying to learn things. Can you tell me specific solution based upon the
SQL
i have posted? Thanks again.


=============

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

I have one [Date] and two time fields [start] and [finish]. The time
cycle
is
either 9am-9pm OR 9pm-9am, so in one case the date of [finish] changes
and
in
another it doesn't. I guess if I had two date fields [st_date] and
[end_date], it will be easier to perform calculations. Unfortunately
end_date
is not given.

I am able to get the results in "HH:MM" (11:40) format through the
following
statement.

Expr1: Format((DateDiff("n",Format([Date],"mm/dd/yyyy") & " " &
[Start],Format([Date]+1,"mm/dd/yyyy") & " " & [Finish])/1440),"Short
Time")

Question:
1. How do I get time in minutes? (like 700 min.)
2. Am I using the right method or there is a better way to do this?

I also tried adding "hh:nn" into date format, it gives an error.

Any suggestion is highly appreciated. Thanks in advance.

 

[Allen Browne]

If you have a negative result, you either have the fields the wrong way
around, the data in the fields is not what you expect, or you have both a
date and a time value in the field (even if that is masked, e.g. by a Format
of "Short Time".)

If there is no date component in the field, you can get the number of
minutes since midnight as:
DateDiff("n", #0:00:00#, [MyField])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tried this but gives values in negative. Is there any way that I can
convert
"HH:MM" (like 11:30) into "Minutes" (like 690 Minutes)?

Thank you.
----------

To get the number of minutes, just use DateDiff():
Expr1: DateDiff("n",[Date],[Finish])

If it still fails, it might be because DATE is a reserved word. I imagine
you would get away with it in this particular case, but it will give you
problems in other scenarios. You could add the table name, e.g.:
Expr1: DateDiff("n",[Table1].[Date],[Finish])

Thanks Allen for your quick response. I checked the link but did not
get
any
specific help that I need. Excuse my inexperience in the subject. I am
trying to learn things. Can you tell me specific solution based upon
the
SQL
i have posted? Thanks again.


=============

:

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

I have one [Date] and two time fields [start] and [finish]. The time
cycle
is
either 9am-9pm OR 9pm-9am, so in one case the date of [finish]
changes
and
in
another it doesn't. I guess if I had two date fields [st_date] and
[end_date], it will be easier to perform calculations. Unfortunately
end_date
is not given.

I am able to get the results in "HH:MM" (11:40) format through the
following
statement.

Expr1: Format((DateDiff("n",Format([Date],"mm/dd/yyyy") & " " &
[Start],Format([Date]+1,"mm/dd/yyyy") & " " & [Finish])/1440),"Short
Time")

Question:
1. How do I get time in minutes? (like 700 min.)
2. Am I using the right method or there is a better way to do this?

I also tried adding "hh:nn" into date format, it gives an error.

Any suggestion is highly appreciated. Thanks in advance.

 

[Guest]

Allen,
Finally, you made my day! The conversion from hours to minutes made things
easier. The query works fine and the puzzle is solved for now. Thank you very
much Allen. See you again with some more questions.

-------------------------------------------------

If you have a negative result, you either have the fields the wrong way
around, the data in the fields is not what you expect, or you have both a
date and a time value in the field (even if that is masked, e.g. by a Format
of "Short Time".)

If there is no date component in the field, you can get the number of
minutes since midnight as:
DateDiff("n", #0:00:00#, [MyField])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tried this but gives values in negative. Is there any way that I can
convert
"HH:MM" (like 11:30) into "Minutes" (like 690 Minutes)?

Thank you.
----------

To get the number of minutes, just use DateDiff():
Expr1: DateDiff("n",[Date],[Finish])

If it still fails, it might be because DATE is a reserved word. I imagine
you would get away with it in this particular case, but it will give you
problems in other scenarios. You could add the table name, e.g.:
Expr1: DateDiff("n",[Table1].[Date],[Finish])

Thanks Allen for your quick response. I checked the link but did not
get
any
specific help that I need. Excuse my inexperience in the subject. I am
trying to learn things. Can you tell me specific solution based upon
the
SQL
i have posted? Thanks again.


=============

:

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

I have one [Date] and two time fields [start] and [finish]. The time
cycle
is
either 9am-9pm OR 9pm-9am, so in one case the date of [finish]
changes
and
in
another it doesn't. I guess if I had two date fields [st_date] and
[end_date], it will be easier to perform calculations. Unfortunately
end_date
is not given.

I am able to get the results in "HH:MM" (11:40) format through the
following
statement.

Expr1: Format((DateDiff("n",Format([Date],"mm/dd/yyyy") & " " &
[Start],Format([Date]+1,"mm/dd/yyyy") & " " & [Finish])/1440),"Short
Time")

Question:
1. How do I get time in minutes? (like 700 min.)
2. Am I using the right method or there is a better way to do this?

I also tried adding "hh:nn" into date format, it gives an error.

Any suggestion is highly appreciated. Thanks in advance.