SonoftheSun
-
Posts
1 -
Joined
-
Last visited
Reputation Activity
-
SonoftheSun reacted to Vitalikssssss in Total Hours Worked / Datediff
I have tweaked the formula provided by @MayMusic further since some time I need zero in front of single-digit hour e.g. 7:00.
So, for the total&aggeration field it looks like this:
(CASE WHEN LEN(CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:End_time]))/3600) AS VARCHAR(6))) < 2 THEN '0'+ CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))/3600) AS VARCHAR(6)) ELSE CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))/3600) AS VARCHAR(6)) END) + ':' + (CASE WHEN LEN(CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))%3600/60) AS VARCHAR(6))) < 2 THEN '0'+ CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))%3600/60) AS VARCHAR(6)) ELSE CAST ((SUM(DATEDIFF(SECOND, [@field:Start_time], [@field:Hora_fin]))%3600/60) AS VARCHAR(6)) END) Hope it would help someone.
Regards,
vitalikssssss
-
SonoftheSun reacted to JolliBeng in Get month name
Hi @Role21,
Yes, there's a way to do that. You just need to use the following formula on your calculated field:
(DATENAME(month, [@field:DATE])) -JolliBeng
-
SonoftheSun reacted to Aether in The week for the month
Hi @FinTheHuman - In addition for the earlier response, you can also use this formula to get the week number with the corresponding month via DataPage level:
CASE WHEN (DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1) = 1 THEN 'Week 1 of ' + (DATENAME(month, [@field:DATE])) WHEN (DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1) = 2 THEN 'Week 2 of ' + (DATENAME(month, [@field:DATE])) WHEN (DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1) = 3 THEN 'Week 3 of ' + (DATENAME(month, [@field:DATE])) WHEN (DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1) = 4 THEN 'Week 4 of ' + (DATENAME(month, [@field:DATE])) ELSE 'Week 5 of ' + (DATENAME(month, [@field:DATE])) END ~WatashiwaJin~
