Solved Caculate the business day based on date time field (1 Viewer)

[Jomat]

My apologies, I might have placed this in the wrong category.

I have a form with some transactions. The fields are ID, BusinessDatetime,businessday,amount,type.

Right now I don't have anything in the businessday field. It is blank because I don't know how to capture the date based on what the businessDateTime field.

I'm trying to get the data to looks like this (without the -) :

1 - 01/03/2024 2:59am - 01/02/2024 - $1.25
2 - 01/03/2024 3:01am - 01/03/2024 - $1.56

I can't figure out how to create a formula on the input form to push the 01/01/2024 into the businessday field.
1. The condition is that if a transaction occurs prior to or at 3am then it belongs to the prior day.
2. If the transaction occurs after 3am but then it belongs to today's business day.
3. Example: One business day is from 01/01/2024 3am to 01/02/2024 3am.

Originally I have a BDate and BTime fields instead of the 01/01/2024 3:01pm field
But I couldn't get my query to pull from the separate date and time fields so I opted for the datetime field.
Thanks ahead of time for any inputs and help.

 

[Gasman]

I would use TimeValue() and subtract 1 from the date if result is less than 3/24

What happens when it is dead on 3AM?

 

[Jomat]

I would use TimeValue() and subtract 1 from the date if result is less than 3/24

What happens when it is dead on 3AM?

Right on 3:00am it would fall back to the prior business day. 3:01am would go to the current business day.

 

[bob fitz]

Try:

=IIf(Hour([BusinessDatetime])<=3,DateValue([BusinessDatetime])+1,DateValue([BusinessDatetime]))

as the Control Source of [businessday]

 

[CJ_London]

I would subtract 0.125 from your business date field to move it back to the businessday date(or use dateadd("h",-3, BusinessDatetime))

Your example is too short to be sure but you might also want to look at the sql partition syntax for your groupings - see

Partition Function - Microsoft Support

support.microsoft.com

Partition function - Access - SS64.com

ss64.com

 

[Gasman]

Try:

=IIf(Hour([BusinessDatetime])<=3,DateValue([BusinessDatetime])+1,DateValue([BusinessDatetime]))

as the Control Source of [businessday]

Forgot about Hour()
Though the O/P would need to subtract 1 not add 1.

 

[bob fitz]

I've looked at this again. The following works for me:
=IIf(Hour([BusinessDatetime])*60+Minute([BusinessDatetime])<=180,[BusinessDatetime],[BusinessDatetime]+1)

 

[Gasman]

@bob fitz
This is now

? hour(now())
 19

Multiplying that by 60 is not a good idea, I would have thought?@

? Hour([Now()])*60+Minute([Now()])
 1162

Time is 19:12

Your initial Hour() hit the nail on the head, I would have thought?

 

[bob fitz]

@bob fitz
This is now

? hour(now())
 19

Multiplying that by 60 is not a good idea, I would have thought?@

? Hour([Now()])*60+Minute([Now()])
 1162

Time is 19:12

Your initial Hour() hit the nail on the head, I would have thought?

I don't think my original suggestion gave the desired results when the time was 03:00 am

Don't understand why you are making comments on the use of Now() function. I haven't used it

 

[The_Doc_Man]

Actually, it is pretty easy. Remember that a date/time value (Access datatype Date) actually is days and fractions of a day. The cutoff is 0300 hours, which is to say, you back it off by 3 hours (out of 24), so 3/24 = 1/8 = 0.125000, which is an easy and exact fraction.

Therefore, the BUSINESS day is merely the transaction date(/time) minus 0.1250, and from that point you can do various things to isolate the date.

To store the business date separately as a timeless date, use this formula (which is actually a bit pedantic, because I think you COULD skip the CDBL() part.)

BusinessDay = CDate( CDBL( FIX ( TransactionDate - 0.125 ) ) )

To merely show the date without actually trying to store it, use

Format( ( TransactionDate - 0.125 ), "dd-MMM-yyyy" )

The trickier part is what happens on weekends. That is a separate question, but one that should be considered, I guess.

 

[Gasman]

I don't think my original suggestion gave the desired results when the time was 03:00 am

Don't understand why you are making comments on the use of Now() function. I haven't used it

Bob, that is just me using a readily available date and time as I do not have BusinessDateTime to hand.

I don't think my original suggestion gave the desired results when the time was 03:00 am

I think it does? Just a simple test to see if the hour is less than 3 or not.

 

[Jomat]

Actually, it is pretty easy. Remember that a date/time value (Access datatype Date) actually is days and fractions of a day. The cutoff is 0300 hours, which is to say, you back it off by 3 hours (out of 24), so 3/24 = 1/8 = 0.125000, which is an easy and exact fraction.

Therefore, the BUSINESS day is merely the transaction date(/time) minus 0.1250, and from that point you can do various things to isolate the date.

To store the business date separately as a timeless date, use this formula (which is actually a bit pedantic, because I think you COULD skip the CDBL() part.)

BusinessDay = CDate( CDBL( FIX ( TransactionDate - 0.125 ) ) )

To merely show the date without actually trying to store it, use

Format( ( TransactionDate - 0.125 ), "dd-MMM-yyyy" )

The trickier part is what happens on weekends. That is a separate question, but one that should be considered, I but it works extremely well. Thank you.

 

[Jomat]

Doc your formula works very well. I didn't know how to create the syntax. Thank you very much.
Thank you everyone.

 

[CJ_London]

Same as the suggestion in post #5. Don’t know why I bother

 

[Jomat]

Same as the suggestion in post #5. Don’t know why I bother

Thank you. I didn't know how to write the iif statement.
Not the iif statement, I meant to say the syntax or where to apply it to.

 

[Gasman]

There is no iif when you just deduct 0.125

 

[Jomat]

There is no iif when you just deduct 0.125

You are correct. I edited my response. I needed Doc's explanation of here's the wrench to fix your problem and this is why and how you should use it.

 

[The_Doc_Man]

Sorry, CJ - you were certainly first - but sometimes you have to explain stuff step by agonizing step.

 

[arnelgp]

here is another sql:

SELECT 
     ID, 
     BusinessDatetime, 
     DateValue(BusinessDateTime) + (TimeValue(BusinessDateTime) < #3:00#) As businessday,
     amount,
     type  
From YourTableNameHere;