Access Round down not working 100%

[mahmed1]

Hi All,

What im trying to do is round down to the nearest half half hour

so if createddatetime is say 03/07/2020 00:05:27 then the period should fall in 00:00:00
if its 00:31:00 then it should fall in 00:30:00
if its 01:01:00 then 01:00:00, if 01:59:57 then 01:30

Hope this makes sense as occasionally the formula below is catagorising into 00:05 and 00:25etc

This is my Formula in VBA

CreatedPeriod = FORMAT(CStr(ROUND(([CreatedDateTime] - INT([CreatedDateTime]))*48,0)/48), ""hh:nn"")

 

[mole999]

Try > How do I simulate Excel's Ceiling/Floor Functions in VBA? - Microsoft: Access Modules (VBA Coding) FAQ - Tek-Tips

 

[mahmed1]

Thank you
Would i use 0.5 as my significance for my example?

 

[mahmed1]

FORMAT(CStr(Floor(([CreatedDateTime] - INT([CreatedDateTime])),0.5) ""hh:nn"")

is that right

 

[mahmed1]

Or is the right syntax

FORMAT(CStr(Floor(([CreatedDateTime] - INT([CreatedDateTime])),30/1440 ""hh:nn"")

Thank you and appreciated

 

[mole999]

haven't used access for years, so i don't have a great answer. 0.5 would be 12 hours in excel, as 1 = a day. in excel you have the option of TIME(00,30,00), i couldn't guess how to manipulate the values for your need

 

[mahmed1]

Thank you hopefully someone can confirm the right syntax to use in access

 

[welshgasman]

I tend to start with Google?

That led me to this link and that produced pretty much what you are after I believe?

HTH

 

[mahmed1]

thank you
Surely i can use Moleys suggestion but still unsure what the right syntax is

 

[mahmed1]

In the other thread it says this but this doesn’t always rounddown
TimeSerial(Hour([mytime]),(Minute([mytime])\30)*30,0)

my created date time has a date and time in there so my aim was to take the time from
That and put into a category by rounding down to nearest half hour

i thought it would be a lot simpler to do like in excel using the Floor Function

any help with my example would really be appreciated

thank you