converting datetime into a decimal

# Thread: converting datetime into a decimal

1. Senior Member
Join Date
Dec 1969
Posts
186

## converting datetime into a decimal

I have a function i use in excel and i would like to use the same function in C#. However, in excel all date times are decimals, while in C# this is not the case. How can i convert the datetime into a decimal so i can perform calculations on it and then convert it back to a datetime. <BR><BR>Convert.ToDecimal(datetime) throws an invalid cast exception error...but its available to use. why would msft do that if its not gonna work? <BR><BR>thanks for any help.

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## What kind of calculations?

Almost surely there is a better way to go.<BR><BR>For starters, decimal arithmetic is slow and clumsy. Double precision floating point would be better. If you describe the calculations you do, that would be the most help.<BR>

3. Senior Member
Join Date
Dec 1969
Posts
186

## heres the function i wanna create

in excel vb:<BR><BR>Function SHIFT(Optional DateTime)<BR><BR> If IsMissing(DateTime) Then<BR> DateTime = Now()<BR> End If<BR><BR> dayornight = (Int((DateTime - 0.25) * 2) Mod 2)<BR> dayofweek = (Int(DateTime - 0.25) / 7 - Int((DateTime - 0.25) / 7)) * 7<BR><BR> If dayofweek = 0 Then<BR> dayofweek = 7<BR> End If<BR><BR> week = Int(DateTime - 0.25) Mod 2<BR><BR> &#039;If Sun, Mon or Tue<BR> If dayofweek &#060; 3.5 Then<BR> If dayornight = 0 Then<BR> SHIFT = "A Shift"<BR> Else<BR> SHIFT = "B Shift"<BR> End If<BR><BR> &#039;If Thu, Fri or Sat<BR> ElseIf dayofweek &#062; 4.5 Then<BR> If dayornight = 0 Then<BR> SHIFT = "C Shift"<BR> Else<BR> SHIFT = "D Shift"<BR> End If<BR><BR> &#039;Else Wed<BR> Else<BR> If week = 1 Then<BR> If dayornight = 0 Then<BR> SHIFT = "A Shift"<BR> Else<BR> SHIFT = "B Shift"<BR> End If<BR><BR> Else<BR> If dayornight = 0 Then<BR> SHIFT = "C Shift"<BR> Else<BR> SHIFT = "D Shift"<BR> End If<BR> End If<BR> End If<BR>End Function<BR><BR>as you can see it requires decimals to work...so either i need to figure out another way using the datetime class in C# or convert it to excel. Any suggestions?<BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## That's a silly function...

...even in Excel!!!<BR><BR>Done right, using VBA built-in functions:<BR><BR>Function SHIFT(Optional DateTime)<BR> If IsMissing(DateTime) Then<BR> DateTime = Now()<BR> End If<BR><BR> isNight = Hour(DateTime) &#060; 6 OR Hour(DateTime) &#062;= 18<BR> dayofweek = Weekday(DateTime)<BR> &#039; this makes no sense! week just alternates 0 to 1 <BR> &#039; from ONE DAY to the next, nothing to do with which actual<BR> &#039; week it is! Oh, wait! I see...if Wednesday is an even day,<BR> &#039; then this is an even week, etc. Okay...hokey, because it<BR> &#039; doesn&#039;t handle month turnover correctly, but w.t.h.<BR> week = Int(DateTime - 0.25) Mod 2<BR><BR> &#039;If Sun, Mon or Tue or odd Wednesday<BR> If dayofweek &#060;= 3 OR ( dayofweek = 3 AND week = 1 ) Then<BR> If isNight Then<BR> SHIFT = "A Shift"<BR> Else<BR> SHIFT = "B Shift"<BR> End If<BR> &#039;If Thu, Fri or Sat<BR> ElseIf dayofweek &#062;= 4 OR ( dayofweek = 3 AND week = 0 ) Then<BR> If isNight Then<BR> SHIFT = "C Shift"<BR> Else<BR> SHIFT = "D Shift"<BR> End If<BR> End If<BR>End Function<BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Now it should be easy...

...to convert to VB.NET (I&#039;ll let you make the conversion to C#; it&#039;s just syntax changes; library functions are same.)<BR><BR>In last post, I screwed up: Should be dayofweek=4 for the Wednesday tests.<BR><BR>Also the ELSEIF should obviously be just ELSE.<BR><BR>************<BR><BR><BR>Function SHIFT(dt As DateTime) AS String<BR><BR> Dim isNight As Boolean = dt.Hour &#060; 6 OR dt.Hour &#062;= 18 <BR> Dim dayofweek As Integer = dt.DayOfWeek &#039; 0=sunday, 6=saturday <BR><BR> &#039; should have done something like this in original! <BR> Dim isOddDate As Boolean = ( dt.AddHours(-6).Day MOD 2 = 1 )<BR><BR> &#039;If Sun, Mon or Tue or odd Wednesday <BR> If dayofweek &#060;= 2 OR ( dayofweek = 3 AND isOddDate ) Then <BR> If isNight Then <BR> Return "A Shift" <BR> Else <BR> Return "B Shift" <BR> End If <BR> End If<BR> &#039;If Thu, Fri or Sat or even Wed<BR> &#039; no need for an ELSE because other paths have done RETURN already<BR> If isNight Then <BR> Return "C Shift" <BR> Else <BR> Return "D Shift" <BR> End If <BR>End Function <BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## And I'll fix my silly mistakes

Function SHIFT(Optional DateTime) <BR> If IsMissing(DateTime) Then <BR> DateTime = Now() <BR> End If <BR><BR> isNight = Hour(DateTime) &#060; 6 OR Hour(DateTime) &#062;= 18 <BR> dayofweek = Weekday(DateTime) <BR> temp = DateAdd("h",-6,DateTime)<BR> isOddDay = ( temp Mod 2 = 1 )<BR><BR> &#039;If Sun, Mon or Tue or odd Wednesday <BR> If dayofweek &#060;= 3 OR ( dayofweek = 4 AND isOddDay ) Then <BR> If isNight Then <BR> SHIFT = "A Shift" <BR> Else <BR> SHIFT = "B Shift" <BR> End If <BR> &#039; any other time: (Thu, Fri, Sat, even Wed)<BR> Else <BR> If isNight Then <BR> SHIFT = "C Shift" <BR> Else <BR> SHIFT = "D Shift" <BR> End If <BR> End If <BR>End Function <BR>

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## Nuts...

This line:<BR> isOddDay = ( temp Mod 2 = 1 )<BR>should be:<BR> isOddDay = ( Day(temp) Mod 2 = 1 )<BR>

8. Senior Member
Join Date
Dec 1969
Posts
186

## RE: That's a silly function...

ya the funky part is how the wednesday isnt consistent every week and alternates. <BR><BR>I didn&#039;t even think about just extracting the part i needed and then doing the various calculations functions.<BR><BR>I&#039;m gonna work on this. <BR><BR>Thanks Bill.<BR><BR>samir.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•