10 Replies Latest reply: May 15, 2014 10:06 AM by Joe Berry

# 2nd Shift Payroll Shows As A Negative

I'm using Monarch 9.00 & Excel 2002.

The 2nd shift payroll data that goes past midnight shows as a negative.

The time from 8:00 pm to 1:00 am shows as (19.00) hours.

I used a calculated field to obtain the number of hours worked.

Thanks for any help,

LL

• ###### 2nd Shift Payroll Shows As A Negative

Yo Dawg, welcome to the forum.

You could use another calculated field (FinalHours ?) to determine that your Hours field that returns -19 should be 5 hours by using:

If(Hours<0,24-Abs(Hours),Hours)[/CODE]

Then negative hours will return the proper amount, and positive hours are assumed to be accurate.

Will that sort of approach work for you?

Data Kruncher

• ###### 2nd Shift Payroll Shows As A Negative

I would be tempted to bite the bullet and set up something for this that you can re-use often by storing it as a USer Defined Function.

I would base the final calculation on the AGE() function. To get to that point your comparisons need to include DATE as well as TIME - i.e. your Date field needs to include the time element.

Now, how you get to that point of the calculation from the information in the report is the key to a successful outcome. Monarch offers several functions to assist and in all cases you are heading towards a value that will be stored measured in seconds and then converted back to values as you wish to show them (Hours and minutes or decimal hours, typically). Using the AGE() function makes this very easy - or should do if the source information is consistent on the report.

If you don't[/B] have an end date to read directly from the report maybe you can assume that the relevant date for the period to end would be date +1 (i.e. the next date) in which case the time value check that Kruncher suggested would apply but be slightly modified to give

(1)[/CODE]

as one of the results of an IF() comparison.

HTH.

Be sure to let us know if the report proves to be reluctant to help you!

Grant

• ###### 2nd Shift Payroll Shows As A Negative

The If statement worked.

Until today's payroll - 2nd shift worked past midnight but instead of clocking out @1:00 or 2:00 a.m (which it recognizes

and I get the correct data) the employee clocked out @12:30 am and the stop time shows as null.

Thanks

• ###### 2nd Shift Payroll Shows As A Negative

The IF statement worked but a new issue has developed. When the 2nd shift

works past midnight and clocks out @1:00 or 2:00 am (I get the correct data)

but if it is 12:30 am - the report data shows as 30 and in monarch as null.

LL

• ###### 2nd Shift Payroll Shows As A Negative

Hello LL Data Dawg,

Presuming the field is called at the moment and you've set it to the data type Time, then the fix for this would be to rename and set it as type Character.

Then in the table, create a calculated field called , data type Time, and in the formula, use the expression:

if(instr(":";intrim())>0;

ctod(intrim();/check the help file for the right option/);

ctod("00:"+intrim();/check the help file for the right option/))[/CODE]

In English, you're telling Monarch to create a Time field based on the report data, checking whether there's a ":" in the report data. If there is, then it just converts CharacterTODate (ctod) from the time as we know it's well-behaved. If not, then it adds "00:" to the left hand side of the report data and then performs the ctod conversion.

The manual is worth checking as ctod has options for input and output formats, as far as I can recall. Apologies for not having a complete solution but I'm not in front of Monarch right now.

Best wishes,

Olly

PS Just checked the manual, from the string, produce a date/time using CtoD, then you can extract just the time portion of this using Time().

• ###### 2nd Shift Payroll Shows As A Negative

Not sure I want to try this b/c affects my other calculated fields which have

the correct data.

Any other suggestions?

LL

• ###### 2nd Shift Payroll Shows As A Negative

At this stage, it would really help if you could post a brief sample of your report. Be sure to scrub any confidential text, but numbers are pretty safe to leave alone, short of personal ID numbers.

Put your sample between and tags (without the spaces) to make sure that the sample appears properly in your forum post.

• ###### 2nd Shift Payroll Shows As A Negative

Start   Stop

14:10  19:30

19:30  20:00

20:00      30

The time is entered as 12:30 & a "1" is used to distinguish am

LL

• ###### 2nd Shift Payroll Shows As A Negative

Not sure I want to try this b/c affects my other calculated fields which have

the correct data.

Any other suggestions?

LL[/quote]

It reads like the only problem you have is that any end times up to 01:00 (AM) are formatted om the report without the leading "00:" before the minutes. Is that correct?

If so you have to work with something that will deal with that isuue, no matter how rare it may be. Olly's proposal of an IF() based formula that comes into play and does something when the problem appears would be the typical way to deal with it. There are likely to be several variations that one might apply to get the result, all of which would be equally valid.

The ultimate use of the AGE() function would still require a clear indication of the time - which is the thing you don't have from the report as it stands. Could you get the report modified to give you consistent formats for times? It's the only other general option I can think of as an alternative to some form of checking along the lines that Olly has proposed.

HTH.

Grant

• ###### 2nd Shift Payroll Shows As A Negative

LL,

It appears to me that the data for the stop time isn't formatted correctly as time.  I base that on the sample above that has no ":" in front of the 30.  If this is true, a way to uncover this error would be to verify the template where that data field is defined.

Olly's solution will fix that.  You also might be able to define another field that will format the stop time for only those instances where the stop time is between midnight and 1am.  That formula for that field might be:

If(Search_XL(":",Stop,1)>0,ctod(Stop),ctod("00"":"Stop))

In your time definitions substitute the New-Stop for Stop and your time calcs will be correct.

Joe