Skip to main content

error in formula to calculate month start and end date

  • January 19, 2024
  • 2 replies
  • 27 views
N
Forum|alt.badge.img+1

I have a table with work months like here:

 

I'm trying to calculate the start and end datetimes of the month but the formula is giving inconsistent answers. It's correct for some months but there's an error in End Dates for March, May, July, October and December.

Here is the formula I'm using for Start and End dates:

Start Date:
DATEADD(DATETIME_PARSE(YEAR({Work Month}) & "-" & MONTH({Work Month}) & "-01", 'YYYY-MM-DD'),-7,'hour')
 
End Date:
DATEADD(DATEADD({Start Date},1,'month'),-1,'second')

What am I doing wrong?

2 replies

dilipborad
Forum|alt.badge.img+23
  • dilipborad
  • Brainy
  • January 19, 2024

Hello @Neptune292,

If the formatting options and timezone don't matter for you then use these simple formulas. Like this. Then format the date using default formatting options or using DATETIME_PARSE

Start Date:

DATETIME_PARSE({Work Month})

End Date: Same as your's

DATEADD(DATEADD({StartDate},1,'month'),-1,'second')

The wrong you've done is you minus -7 hours from the current date. Which affects on the next field as well.

See this screenshot with or without that -7 hour.

I hope this helps.

👍

N
Forum|alt.badge.img+1
  • Neptune292
  • Author
  • New Participant
  • January 20, 2024

Hello @Neptune292,

If the formatting options and timezone don't matter for you then use these simple formulas. Like this. Then format the date using default formatting options or using DATETIME_PARSE

Start Date:

DATETIME_PARSE({Work Month})

End Date: Same as your's

DATEADD(DATEADD({StartDate},1,'month'),-1,'second')

The wrong you've done is you minus -7 hours from the current date. Which affects on the next field as well.

See this screenshot with or without that -7 hour.

I hope this helps.

👍


thanks for your response.

timezone matters for me. i'm trying to set the timezone to my timezone that's why I did the minus 7.

I don't know why the formula calculates it correctly for some months but not others

I was able to fix it by setting the End Date to this:

DATEADD(DATEADD(DATEADD(DATETIME_PARSE(YEAR({Work Month}) & "-" & MONTH({Work Month}) & "-01", 'YYYY-MM-DD'),1,'month'),-1,'second'),-7,'hour')
Terms & ConditionsAccessibility statement